How to implement GROUP_CONCAT in Laravel model class?

Question

I'm using GROUP_CONCAT function to fetch multiple photo image paths for the selected professional in Laravel. The code is in a model class. The code segment is as below:

$professionals = DB::table('member_professional')
    ->select("member_professional.*",
        DB::raw("(GROUP_CONCAT(DISTINCT photos.picture_path SEPARATOR ',')) as 'photos'"))
    ->join('member','member_professional.member_id_fk','=','member.member_id')
    ->join('photos','photos.member_fk','=','member.member_id')
    ->leftjoin('locations','member.city','=','locations.location_id');

        if ($professionals_type != '') {
                     $professionals = $professionals->where('member_professional.biz_category','=',$professionals_type);
        }

        if ($location != '') {
                     $professionals = $professionals->where('member.city','=',$location);
        }

    $professionals = $professionals->where('member.member_type','=',2); 
    $professionals = $professionals->groupBy('member_professional.member_id_fk')
    ->paginate(20);

When I run it on the staging server: http://dev.interioradditions.pk/allProfessionals it throws the error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'interior_dev_db.member_professional.biz_name' isn't in GROUP BY (SQL: select member_professional.*, (GROUP_CONCAT(DISTINCT photos.picture_path SEPARATOR ',')) as 'photos' from member_professional inner join member on member_professional.member_id_fk = member.member_id inner join photos on photos.member_fk = member.member_id left join locations on member.city = locations.location_id where member.member_type = 2 group by member_professional.member_id_fk limit 20 offset 0)

Whereas when I extract the built sql query from the above statement and execute through PHPMyAdmin it runs fine and gives the result as expected. Please help on getting what's wrong in the Model query.

Thanks!

It would be very easy if you set up models and relationship then eloquent will do rest of the job and there would be no need of GROUP_CONCAT — M Khalid Junaid
– M Khalid Junaid, Commented May 21, 2018 at 9:52
Can you please help how to use group by clause for this particular case? — Babur Mansoor
– Babur Mansoor, Commented May 22, 2018 at 3:33

M Khalid Junaid · Accepted Answer · 2018-05-22 06:19:10Z

1

What i prefer going through laravel's approach by defining your models and relationship between them

class MemberProfessional extends Model
{  
    public function member()
    {
        return $this->hasMany('App\Member', 'member_id', 'member_id_fk');
    }
}

class Member extends Model
{
    public function photos()
    {
        return $this->hasMany('App\Photos', 'member_fk', 'member_id');
    }    
    public function locations()
    {
        return $this->hasMany('App\Locations', 'location_id', 'city');
    }   
    public function member_professional()
    {
        return $this->belongsTo('App\MemberProfessional', 'member_id_fk');
    }   
}

class Photos extends Model{
//...
}

class Locations extends Model{
//...
}

And then you could write simple eloquent query

$professionals = MemberProfessional::with('member.photos');

if ($professionals_type != '') {
    $professionals = $professionals->where('biz_category','=',$professionals_type);
}

if ($location != '') {
    $professionals->whereHas('member', function ($query) use ($location) {
                $query->where('city', '=', $location);
                  });
}
    $professionals->whereHas('member', function ($query) use ($location) {
                $query->where('member_type', '=', 2);
                  })
                  ->paginate(20);

Now for each row you will have collection of related Photos model object

answered May 22, 2018 at 6:19

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Babur Mansoor Over a year ago

Great. Looks like the best (and clean) solution using Laravel model structure. Sorry for I was busy with some other tasks. Anyways I'll apply this solution and let you know. Many many thanks for this help. :)

M Khalid Junaid · Accepted Answer · 2018-05-22 05:52:56Z

As per the new release ONLY_FULL_GROUP_BY mode is by default enabled and you need to use a complete GROUP BY expression like all the columns present in your select list (except aggregated ones) should be present in GROUP BY like

select a,b, count(c)
from table
group by a,b

In your case you need to specify which columns you need from member_professional like

$professionals = DB::table('member_professional as mp')
    ->select('mp.member_id_fk', 'mp.col1',  'mp.col2',  'mp.col3', DB::raw("(GROUP_CONCAT(DISTINCT p.picture_path SEPARATOR ',')) as 'photos'"))
    ->join('member as m','mp.member_id_fk','=','m.member_id')
    ->join('photos as p','p.member_fk','=','m.member_id')
    ->leftjoin('locations','m.city','=','locations.location_id');

if ($professionals_type != '') {
    $professionals = $professionals->where('mp.biz_category','=',$professionals_type);
}

if ($location != '') {
    $professionals = $professionals->where('m.city','=',$location);
}
$professionals = $professionals->where('m.member_type','=',2)
    ->groupBy('mp.member_id_fk', 'mp.col1',  'mp.col2',  'mp.col3')
    ->paginate(20);

Also note using GROUP_CONCAT "The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet".

Collectives™ on Stack Overflow

How to implement GROUP_CONCAT in Laravel model class?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related