Laravel Eloquent Multiple Where with count

Question

Following is the SQL query that I need to perform on laravel eloquent. SQL returns expected output.

SELECT
  orders.id,
  orders.`status`,
  order_type,
  COUNT(order_type) as count
FROM
  orders
WHERE
  orders.`status` = 0 && order_type = 1
ORDER BY
  orders.id DESC

what I have tried on laravel is below

        $receved = Order::select('status', 'order_type')->where('status',0);
        $relase = $receved->where('order_type',  1)->get();
        $bulk = $receved->where('order_type', 2)->get();
        $distiribute = $receved->where('order_type', 3)->get();

        return response()->json([

            'success' => true,
            'message' => 'Statement Updated',
            'orderStatment' =>  [
                'relaseCount' => count($relase),
                'bulkCount' =>  count($bulk),
                'distiributeCount' => count($distiribute)
            ],
        ], 200);

I seeking recommendation/suggestion to operate this in a correct way

The output I getting on laravel is

            'orderStatment' =>  [
                'relaseCount' => 14,
                'bulkCount' =>  0,
                'distiributeCount' => 0
            ],

the output of expectation and SQL produce is

                    'orderStatment' =>  [
                        'relaseCount' => 14,
                        'bulkCount' =>  5,
                        'distiributeCount' => 4
                    ],

There are 8 Different type of status and 3 different types of order_type available on Table I want to get each order_type count of every status

Answer 1

The problem you're facing is due to the fact that all of the following statements are manipulating the same query builder object:

$receved = Order::select('status', 'order_type')->where('status',0);
$relase = $receved->where('order_type',  1)->get();
$bulk = $receved->where('order_type', 2)->get();
$distiribute = $receved->where('order_type', 3)->get();

So the actual queries created will be something like this:

All start with: select status, order_type from orders where status = 0 and

1. order_type = 1;
2. order_type = 1 and order_type = 2;
3. order_type = 1 and order_type = 2 and order_type = 3;

This is why the last two queries return 0. It's expected once you see the resulting query.

You can verify this by logging the query (see this answer for details, or the docs here).

$receved is actually getting the where clauses attached to it each time. So you're not just starting with the original statement, but building onto it each time you call where.

Answer 2

You might have better luck doing it all in one query, then getting the data back out.

$receved = Order::select('status', 'order_type', DB::raw('COUNT(id) as order_count'))->where('status',0)
              ->groupBy('order_type')
              ->get();

This will give you a collection of all of the order types and their counts in one query. After that, you can get the data back out.

$bulk = $relase = $distiribute = 0;
foreach($receved as $rec) {
      if($rec->order_type == 1) $relase = $rec->order_count;
      elseif($rec->order_type == 2) $bulk = $rec->order_count;
      elseif($rec->order_type == 3) $distiribute = $rec->order_count;
}