1

I'm trying to use order by without adding the order column in groupby, it only works if I execute it directly from the database but from laravel I get database error

I made this eloquent code

Comment::select('product_id')->where('shop_name', $shop)->groupby('product_id')->distinct()->orderBy('created_at')->paginate(12)

it will product the following query

select distinct DISTINCT(product_id) from comments where shop_name = 'shopname' group by product_id order by created_at asc limit 12 offset 0

if I rub the above query directly in database it works

but if I use Laravel eloquent code it fires this error

SQLSTATE[42000]: Syntax error or access violation: 1055 'areviews_areviewzappz.comments.created_at' isn't in GROUP BY (SQL: select distinct DISTINCT(product_id) from comments where shop_name = 'shopname' group by product_id order by created_at asc limit 12 offset 0)

how can I solve this issue ?

Ahmadz Issa
  • 669
  • 3
  • 12
  • 36
  • You could add the `created_at` in the `groupBy` – apokryfos Jan 07 '19 at 15:11
  • Whats `distinct DISTINCT(product_id)` ? `DISTINCT` is a keyword not a function in MySQL i wonder why laravel generates this SQL?? Besides DISTINCT is generated double.. "if I rub the above query directly in database it works" Yes it "works" but you can get **invalid data** because it's not ANSI SQL valid.. – Raymond Nijland Jan 07 '19 at 15:59

2 Answers2

2

The issues is that you SHOULD really include the ORDER BY in the GROUP BY list as this is best practise.

The reason it works when you are on the the sql mode set to ''. However, Laravel by default (I think) has Strict as TRUE,

You have 2 options:

  1. Add the created_at to the GROUP BY clause (Recommended)
  2. Change the strict mode to false

A bit more info on the 2nd option:

How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

Petay87
  • 1,700
  • 5
  • 24
  • 39
  • Hello, if I included both I will get different results in Pagination for example. if I used product_id only I get 88 page but If I use product_id with created_at as you suggested I will get 4444 page and that not what I want. I will check the second solution it might be what I need thank you. – Ahmadz Issa Jan 07 '19 at 16:12
  • Changing strict mode to false solved the issue. Thank you very much – Ahmadz Issa Jan 07 '19 at 16:20
0

You can group it after you have retrieved the results rather than in the MySQL query - so you group the collection after it has been retrieved, not the table entries in the query itself.

Comment::select('product_id')
   ->where('shop_name', $shop)
   ->distinct()
   ->orderBy('created_at')
   ->paginate(12)
   ->groupBy('product_id')

Laravel collection groupBy method: https://laravel.com/docs/5.7/collections#method-groupby

Related post: Laravel Query Buider Group By Not Getting All The Records

party-ring
  • 1,761
  • 1
  • 16
  • 38
  • if I do that I will get pagination issue since it will get all the database records for this shop in may case it's 7500 page but if I use group by I will get 88 page only therefor it will not work with the pagination will get wrong data – Ahmadz Issa Jan 07 '19 at 16:10