Как добавить новый столбец в select с объединенным значением из Eloquent Builder ⇐ Php

[Гость]

пожалуйста, будьте вежливы, я новичок в laravel Помогите мне, пожалуйста..
У меня есть

Код: Выделить всё

alokasikegiatan

table:



id_anggota
id_keg
kegiatan




340060602
1
Keg1


340060603
4
Keg4


340060607
1
Keg1


...
...     
 ...      



And i have

Код: Выделить всё

pekerjaan

table:



id_anggota
id_keg
id_pekerjaan




340060602
1
45


340060603
4
68


340060602
1
32


...
...
...



I just want to make new select table, then add column with unique

Код: Выделить всё

id_keg

, with value

Код: Выделить всё

COUNT(id_pekerjaan) as kegiatan

and join on

Код: Выделить всё

id_anggota

like this:



id_anggota
Keg1
Keg4
Keg..




340060602
2
0
...


340060603
0
1
...


340060607
0
0
...


...
...
...
...



So i tried:

Код: Выделить всё

$unique_keg = DB::table('alokasikegiatan')->select('id_keg','kegiatan')->distinct()->get();
$tabel = DB::table('pekerjaan')->select('id_anggota', 'id_keg',
DB::raw('COUNT(id_pekerjaan) as jlh_pekerjaan'))->groupBy('id_anggota', 'id_keg');
$tabel2 = DB::table('pekerjaan')->select('id_anggota')->distinct();

// iterate over $unique_keg
$result = $tabel2;
for ($i = 0; $i < count($unique_keg); $i++) {

// filtering specific $unique_keg[$i]->id_keg
$query = DB::table($tabel,'t1')->where('t1.id_keg','=',$unique_keg[$i]->id_keg)
->select('id_anggota','jlh_pekerjaan');
$keg = $unique_keg[$i]->kegiatan;

// join `jlh_pek` value on `id_anggota` column, and named new column as `kegiatan`
$result = DB::table($result,'t2')->select(DB::raw("SELECT id_anggota,
jlh_pekerjaan as '$keg' LEFT JOIN (" . $query->toSql(). ") AS query
ON query.id_anggota = t2.id_anggota"));
}
dd($result->get());

But I got this error:

lluminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

What is the right way to get output that i want? Thanks before.


Источник: https://stackoverflow.com/questions/781 ... nt-builder