How to merge two query into a single query in Laravel?

In my application, I have two similar pages where one page has data with group by and another page has without group by. Here is the code for without group by

 $posts = Product::where('category_id', null)->get();

Here is the code with group by

 $posts = Product::where('category_id','!=', null)->get()->groupBy('category_id');

When group by I need extra two value for count number of items and sum of amount

Now, How can I do this two query into a single query? I don't want to keep two pages for this. Now I want to show all data into one page. For example

product_id, Product name, Count of items(* when category_id is not null), Sum of price( * when category_id is not null)

tisuchi

1 year ago

Level 70

@prince69 You can try this


$posts = Product::select(
        'id as product_id',
        'name as product_name',
        DB::raw('COUNT(*) as item_count'), // Count of items in the group
        DB::raw('SUM(price) as total_amount') // Sum of prices in the group
    )
    ->groupBy(DB::raw('IFNULL(category_id, id)')) // Group by category_id if not null, otherwise by id
    ->get();

prince69

1 year ago

Level 1

@tisuchi When try with groupBy, getting error like " SELECT list is not in GROUP BY clause and contains nonaggregated column, this is incompatible with sql_mode=only_full_group_by"

tisuchi

1 year ago

Level 70

@prince69 I see. It's probably because of MySQL's ONLY_FULL_GROUP_BY mode is enabled.

How about this (untested)?

$posts = Product::select(
        'id as product_id',
        'name as product_name',
        DB::raw('COUNT(*) as item_count'), // Count of items in the group
        DB::raw('SUM(price) as total_amount') // Sum of prices in the group
    )
    ->groupBy('id', 'name', DB::raw('IFNULL(category_id, id)')) // Group by id, name, and IFNULL(category_id, id)
    ->get();

Please or to participate in this conversation.