Group by Laravel?

Question

I have the following query:

$orders = Order::where(function ($query) use ($request) {

            // Filter by in process delivery
            if ($request->get("status") && $request->get("status") == "2") {
                $query->where('status', "2");
            }

        })->groupBy('created_at')->get();

When I try to execute this query I get an error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'delivery.order.id' isn't in GROUP BY (SQL: select * from `order` group by `created_at`)

What is problem?

The problem is you are trying to select a column (or even columns) that is not in group clause. What exactly you are trying to achieve? Why are you grouping it? — pbogut, Dec 17 '16 at 14:55

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

In your config/database.php file, change mysql configuration array

from 'strict' => true

to 'strict' => false

For more information about this error read this answer.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 17 '16 at 15:26

Amit Gupta

17,072
4
41
53

score 0 · Answer 2 · answered Dec 17 '16 at 15:09

The problem is the final query select * from `order` group by `created_at`. It's trying to select every column, but its grouping only one. Your database is working in only full group mode which means all selected fields have to be in group by clause (or have to be aggregator functions like SUM, COUNT etc.)

I don't know what you really want to get from this query but you can select columns manually like this:

$orders = Order::where(function ($query) use ($request) {
    if ($request->get("status") && $request->get("status") == "2") {
        $query->where('status', "2");
    }
})->groupBy('created_at')
  //select columns like this
  ->select(\DB::raw('COUNT(*)'), 'created_at')
  ->get();

Another option is to disable full group mode but the result will be unexpected and probably that's not what you want.

Group by Laravel?

2 Answers2

Linked