Ive got an issue with my application when i add paginate to the statement, it comes up with an error about my group by.
This code works (without pagination):
$productsQuery = Product::where('approved', '=', 1)->leftJoin('reviews', 'reviews.products_id', '=', 'products.id')->select('products.*', DB::raw('AVG(ratings) as ratings_average' )) ->groupBy('id') ->orderBy('ratings_average', 'DESC');
This code does not work (trying to paginate):
$productsQuery = Product::where('approved', '=', 1)->leftJoin('reviews', 'reviews.products_id', '=', 'products.id') ->select('products.*', DB::raw('AVG(ratings) as ratings_average' )) ->groupBy('id') ->orderBy('ratings_average', 'DESC') ->paginate(18);
Error:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in group statement is ambiguous
The error seems to suggest a problem with a group by but i'm presuming its something to do with paginate, when considering it works without. Has anyone experienced this problem and does anyone know how i work around this and get my query to paginate?
First see https://laracasts.com/discuss/channels/laravel/laravels-groupby-not-working
Second pagination with group by usually doesn't make sense, the data has to be iterated over by the query each pass to get the grouped data.
If large result set, post here reply, also backtick your code correct.
If a small resultset, ok.
https://laracasts.com/discuss/channels/general-discussion/guidelines-for-posting-on-laracastscom
You should mention the id in ->groupBy('id') as groupBy('products.id') ;
Your query contain multiple time same column id, so update and check your query with:
productsQuery = Product::where('approved', '=', 1)->leftJoin('reviews', 'reviews.products_id', '=', 'products.id') ->select('products.*', DB::raw('AVG(ratings) as ratings_average' )) ->groupBy('products.id') ->orderBy('ratings_average', 'DESC') ->paginate(18);
Must read below content taken from laravel documentation:
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.
In effect, that's what I'm asking how would I turn my current statement into a manual pagination?
First confirm that error resolved with answer:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in group statement is ambiguous
Second you may get some help from this discussion
Please or to participate in this conversation.