martinszeltins's avatar

Column not found in 'group statement' when using pagination

It looks like when Laravel generates the necessary data for pagination, it overrides the SELECT part of my statement. That means I lose all my selects and aliases and it can't find it in the group clause anymore... :( What can I do?

$orders = Orders::selectRaw('
    date(created_at) as date_of_month,
    weekday(created_at) + 1 as day_of_week,
    sum(order_sum) as order_sum
')->groupBy('date_of_month')
  ->paginate();

And then I get this error:

    message: "SQLSTATE[42S22]:
    Column not found:
    1054 Unknown column 'date_of_month' in 'group statement'  (SQL: select count(*) as aggregate from `orders` group by `date_of_month`)
    exception: "Illuminate\Database\QueryException"
0 likes
1 reply
skauk's avatar
skauk
Best Answer
Level 8

@martinzeltin Pagination won't work like this out of the box because of your query returning groups instead of single records that then can be hydrated into your model. There are ways that you can get around that. Take a look at this gist, it might help you with what you are trying to do.

Please or to participate in this conversation.