Eloquent Order By and Group By

Question

I've been searching for 2 hours on how to build a query with eloquent using groupBy and orderBY.

This is my situation: I have a table orders with these columns:

idOfOrder, quantityOfProduct, userWhoBuy, productBuy, statusOfOrder

I would like to get the 3 most sold products. For this, I need to build a query who group all data from ProductBy, sum theme from quantity and sort theme by desc. But it's terribly difficult.

This my actual query:

    $buyObj->select('product',DB::raw('count(*) as total'))
->where('status','=','1')
->groupBy('product')
->orderBy('quantity','desc')
->take(3)
->get();

I get this error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'web_projet_exia.buys.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select product, count(*) as total from buys where status = 1 group by product order by quantity desc limit 3) "

Do you know how to fix that ?

Thanks in advance !

Why are you trying to order the results? In combination with GROUP BY this has no effect. — Jonas Staudenmeir
@JonasStaudenmeir Thanks for you response. i need to order the result beacause i want the three product with the higtest quantity sold — ebillis

ebillis ebillis · Accepted Answer · 2018-04-18T21:41:39

0

votes

The solution is just to order by total and not by quantity. Thanks to @JonasStaudenmeir

Eloquent Order By and Group By

1 Answers