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

Question

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)

Answer 1

Try this and let me know.

$posts = Product::select(
        'id', 
        'name',
        DB::raw('COUNT(*) as item_count'),
        DB::raw('SUM(price) as total_price')
    )
    ->whereNotNull('category_id')
    ->groupBy('category_id')
    ->get();

N:B :-

Make sure that you have imported use Illuminate\Support\Facades\DB; to the top of the script.