How to Solve Incompatible with SQL_Mode=Only_Full_Group_By in Laravel Eloquent

How to disable only_full_group_by option in Laravel

This is the wrong way of going about this. Rather than turning off only_full_group_by mode, you should be fixing your query so that it doesn't break MySQL:

SELECT a1.*
FROM A a1
INNER JOIN
(
    SELECT A.id
    FROM A
    INNER JOIN B
        ON A.id = B.a_id 
    INNER JOIN C
        ON C.id = B.c_id
    GROUP BY A.id
    HAVING COUNT(A.id) > 0
) a2
    ON a1.id = a2.id;

I don't know why your attempts to turn off strict mode failed, but you should not be relying on it in any case.

groupBy does not work properly for eloquent and query builder in laravel

You have ONLY_FULL_GROUP_BY mode switched on (it is by default set to ON on MySql 5.7+).

This is an improvement done on latest mysql version to avoid removal of rows from the query result. Instead of grouping it with PK, try grouping it with other columns to return proper results.

Or else Disable this mode if you just want to proceed with the results

See - Disable ONLY_FULL_GROUP_BY

Laravel - multiple select query

It is SQL Standard feature and its not anything to do with laravel. You can add required column to group by or use an aggragete function like sum etc.

If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function. These columns can either be the column, an expression, or the ordinal number in the column list.

$top_orders = OrderItem::select('name','productId', DB::raw('count(name) as total'))
  ->groupBy('name','productId')
  ->orderBy('total','desc')
  ->take(5)
  ->get();

---

Related Topics