Problem on group by SQL request in laravel

Question

I have my SQL request which works in phpmyadmin but in the eloquent request it does not work because of the group by

Please help me and thank you in advance!

Code php(Laravel) $top10 = sales::where('id_user', Auth::id())
                                    ->whereYear('created_at', $currentYear)
                                    ->whereMonth('created_at', $currentMonth)
                                    ->selectRaw('id_article, COUNT(*) as count')
                                    ->groupBy('id_article')
                                    ->orderByDesc('count')
                                    ->limit(10)
                                    ->get();
Request SQL : select id_article, COUNT(*) as count, id_client from `ventes` where `id_user` = 2 and year(`created_at`) = 2020 and month(`created_at`) = 06 group by `id_article` order by `count` desc limit 10

You can't include non-grouped by or non aggregated column in select such as `id_client` -- it is better to include `id_user` in group by clause and replace `get` with `->get(['id_article', 'id_user', DB::raw('count(*) as count')]);` -- If you still want to do it without it, check https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen — Ersoy, Jun 09 '20 at 23:02
thanks this solved the problem: 'connections' => [ 'mysql' => [ // Behave like MySQL 5.6 'strict' => false, // Behave like MySQL 5.7 'strict' => true, ] ] But say you know if the problem can appear with pgsql because it's heroku I use in production Cordialemet! — Ly Boubacar, Jun 09 '20 at 23:32
i think, it is better to fix your query/data model to make proper group by. — Ersoy, Jun 09 '20 at 23:34
It is because it does not work that I opted for this solution ... even with the method that you gave — Ly Boubacar, Jun 09 '20 at 23:52

score 0 · Accepted Answer · answered Jun 10 '20 at 00:17

everything seems to be going well now with this request:

$top10 = Ventes::where('id_user', Auth::id())
                                    ->whereYear('ventes.created_at', $currentYear)
                                    ->whereMonth('ventes.created_at', $currentMonth)
                                    ->join('stocks', 'stocks.id', '=', 'ventes.id_article')
                                    ->groupBy('id_article', 'name', 'stocks.username')
                                    ->orderByDesc('count')
                                    ->limit(10)
                                    ->get(['id_article', 'name', 'stocks.username AS username', DB::raw('count(*) as count')]);

all the columns that I select I put them in the group by also

Problem on group by SQL request in laravel

1 Answers1