Question 202398 in Laravel Question posted in
The official Laravel Documentation can be found here.

Select multiple column in groupBy laravel 5.7

I am using Laravel 5.7 and i need to generate top selling products of current month for my reports. So i join two table products and sells. I want three columns in return which are sell_quantity, product_name*, and product_id.

So i pass those three column inside selectRaw and the query is like below:

 $top_items = Sell::whereBetween('date', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])->leftJoin('products', 'products.id', '=', 'sells.product_id')->selectRaw('sum(sells.quantity) as quantity, products.name, product_id')->groupBy('product_id')->limit(10)->orderBy('sells.quantity', 'desc')->get();

But when i execute i got an error which said products.name isn’t in GROUP BY. My question is it required to pass all the parameter in select which i placed inside groupBy.

Tags:

3

Answers


  1. 0

    If I understand the task correctly, then instead of groupBy you can use unique:

    $top_items = Sell::whereBetween(‘date’, [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])->leftJoin(‘products’, ‘products.id’, ‘=’, ‘sells.product_id’)->selectRaw(‘sum(sells.quantity) as quantity, products.name, product_id’)->limit(10)->orderBy(‘sells.quantity’, ‘desc’)->get()->unique(‘product_id’);

    Login or Signup to reply.
  2. 0

    Try this

    $top_items = Sell::whereBetween('date', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])
    ->leftJoin('products', 'products.id', '=', 'sells.product_id')
    ->selectRaw('sum(sells.quantity) as quantity, products.name, sells.product_id')
    ->groupBy('sells.product_id', 'products.name')
    ->limit(10)
    ->orderBy('quantity', 'desc')
    ->get();
    Login or Signup to reply.
  3. 0

    You can achieve this in 2 ways

    1. When using group by, you should include all the selecting fields in the GroupBy function

    $top_items = Sell::whereBetween(‘date’, [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])->leftJoin(‘products’, ‘products.id’, ‘=’, ‘sells.product_id’)->selectRaw(‘sum(sells.quantity) as quantity, products.name, product_id’)->groupByRaw(‘sum(sells.quantity), products.name, product_id’)->limit(10)->orderBy(‘sells.quantity’, ‘desc’)->get();

    1. Disable ONLY_FULL_GROUP_BY, You can disable in Environment variables / extensions

    Not recommended

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search