CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-query-builder
Mr J
Mr J

Reputation: 2973

Laravel Eloquent GroupBy throwing SQL error

When I run the below SQL manually I get my expected results with no error

select * from `crawl_results` 
    where `user_id` = 1 and `website_id` = 1 
    and `item_state` != 'OK' group by `destination_url` 
    limit 30 offset 0

However when I run this in Eloquent...

self::where('user_id', Auth::id())
    ->where('website_id', $scanID)
    ->where('item_state', '!=' , 'OK')
    ->groupby('destination_url')->paginate(30)

It produces this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'link_checker.crawl_results.id' isn't in GROUP BY (SQL: select * from crawl_results where user_id = 1 and website_id = 1 and item_state != OK group by destination_url limit 30 offset 0)

Not sure what is happening behind the abstraction to produce that error?

Upvotes: 0

Views: 244

Answers (2)

Prafulla Kumar Sahu
Prafulla Kumar Sahu

Reputation: 9703

Change your query to a query builder query

DB::table('crawl_results')->where('user_id', Auth::id())->where('website_id', $scanID)->where('item_state', '!=' , 'OK')->groupby('destination_url')->paginate(30);

It should work fine.

In documentation it is mentioned that GROUP BY can not be executed efficiently in pagination

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Upvotes: 1

Mosi
Mosi

Reputation: 198

You should go to config\database.php and change strict to false

'mysql' => [
   ...
   'strict' => false,
   ...
 ]

Upvotes: 4

Related Questions