how to get result of group by with join

Question

I am using query in controller:

$data = DB::table('circulate_files')
           ->join('regionmasters','circulate_files.region_id','=','regionmasters.id')
           ->select('circulate_files.unique_id_for_group,circulate_files.title','regionmasters.region', DB::raw('group_concat(region) as new_region'))
           ->groupBy('unique_id_for_group')
           ->get();

But I am getting error:

Illuminate\Database\QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'circulate_files.unique_id_for_group,circulate_files.title' in 'field list' (SQL: select circulate_files.unique_id_for_group,circulate_files.title, regionmasters.region, group_concat(region) as new_region from circulate_files inner join regionmasters on circulate_files.region_id = regionmasters.id group by unique_id_for_group)

Answer 1

I think the problem in your select, it should be:

select(['circulate_files.unique_id_for_group','circulate_files.title','regionmasters.region', DB::raw('group_concat(region) as new_region')])

the problem was in this code:

->select('circulate_files.unique_id_for_group,circulate_files.title', ...)

it should be:

->select('circulate_files.unique_id_for_group','circulate_files.title',...)

Answer 2

you can rewrite your query as follow and use selectRaw instead of select:

$data = DB::table('circulate_files')
           ->selectRaw('
               circulate_files.unique_id_for_group, circulate_files.title, 
               regionmasters.region, group_concat(region) as new_region
            ')
           ->join('regionmasters', 'circulate_files.region_id', 'regionmasters.id')
           ->groupBy('unique_id_for_group')
           ->get();