0

I have a query which works perfectly fine on mysql client and phpmyadmin, my query is : 

select members.m_id, members.m_reach, members.m_member_num_groups, members.m_name, groups.tr_cat,
    groups.tr_id, group_categories.tc_id, group_categories.tc_name,group_members.tm_owner_id,
    group_members.tm_group_id from members join group_members on group_members.tm_owner_id = members.m_id
    join groups on groups.tr_id = group_members.tm_owner_id
    join group_categories on group.tr_cat = group_categories.tc_id
    group by members.m_id
    order by members.m_reach DESC limit 5

but when I try it with laravel DB::select I get error :

SQLSTATE[42000]: Syntax error or access violation: 1055 'muzikal.members.m_reach' isn't in GROUP BY

this is my laravel query :

$influencers = \DB::table('members')
        ->join('group_members', 'group_members.tm_owner_id', '=', 'members.m_id')
        ->join('groups', 'groups.tr_id', '=', 'group_members.tm_owner_id')
        ->join('group_categories', 'groups.tr_cat', '=', 'group_categories.tc_id')
        ->select('members.m_id', 'members.m_reach', 'members.m_member_num_groups', 'members.m_name', 'groups.tr_cat',
                  'groups.tr_id', 'group_categories.tc_id', 'group_categories.tc_name','group_members.tm_owner_id',
                  'group_members.tm_tribe_id')
        ->orderBy('members.m_reach', 'DESC')
        ->groupBy('members.m_id')
        /**->distinct('m_id') tried this also **/
        ->limit('5')
        ->get();

I have seen many people asking the same question but couldn't get to a proper answer

Khan Shahrukh
  • 6,109
  • 4
  • 33
  • 43
  • try to add muzikal.members.m_reach to `group by` clause like group by members.m_id, muzikal.members.m_reach – A l w a y s S u n n y Nov 07 '16 at 16:49
  • If it works with the straight query, post your Laravel query so we can look at that. – aynber Nov 07 '16 at 16:52
  • @aynber added the laravel code too – Khan Shahrukh Nov 07 '16 at 17:00
  • I suspect the problem is that `muzikal.members.m_reach` isn't in `GROUP BY`. Try adding it. – Álvaro González Nov 07 '16 at 17:01
  • tried that before posting, when I add m_reach it says that another column isn't in GROUP BY and like that it makes me add all the columns into group by and it works but by then the whole usage of group by is lost, then I get ungrouped result @ÁlvaroGonzález – Khan Shahrukh Nov 07 '16 at 17:03
  • You aren't using aggregation functions like `MAX()` or `COUNT(*)`... What's the point of `GROUP BY` in the first place? Do you want to omit rows and you don't care which ones? – Álvaro González Nov 07 '16 at 17:06
  • because I want 5 users with their m_reach ordered in ascending manner with the number of groups they are member of and category of that group – Khan Shahrukh Nov 07 '16 at 17:08
  • Instead of `get()` you could use `toSQL()` and `var_dump` the result to see the query created by laravel so you can compare the query with what you expect it to be, and also to try it out in MySQL – rypskar Nov 07 '16 at 17:21
  • The problem is that if you only group by `members.m_id` you'll get arbitrary rows from the other tables, with no specific filtering criteria. It's very unlikely that you want that. – Álvaro González Nov 07 '16 at 17:27

0 Answers0