Laravel Query CASE with WHEREBETWEEN

Question

I have the following query:

    $ordersAllProducts=Product::groupBy('products.SKU')
        ->selectRaw('products.SKU,
        (CASE
        WHEN sum(orders.quantity)>1 THEN sum(orders.quantity)
        ELSE 0
        END) as quantity_sum')
        ->leftjoin('orders','products.SKU','=','orders.SKU')
        ->get();

It all works great, but I need to return the sum of orders.quantity between certain dates.

I've tried this code:

$ordersAllProducts=Product::groupBy('products.SKU')
            ->selectRaw('products.SKU,
            (CASE
            WHEN sum(orders.quantity)>1 THEN sum(orders.quantity)
            ELSE 0
            END) as quantity_sum')
            ->whereBetween('date', [$from, $toTime->format('Y-m-d H:i:s')])
            ->leftjoin('orders','products.SKU','=','orders.SKU')
            ->get();

It works great but all of a sudden, not all of the fields from products (left table) are joined, i.e.

Before Between Dates (showing sum of quantity for all dates):

+-----+--------+
|SKU  |Quantity|
|ABC  | 10     |
|DEF  | 20     |
|XYZ  | 40     |
+-----+--------+

After Between Dates (now DEF has no orders between the dates)

+-----+--------+
|SKU  |Quantity|
|ABC  | 8      |
|XYZ  | 14     |
+-----+--------+

My desired output is below (showing 0 for DEF)

+-----+--------+
|SKU  |Quantity|
|ABC  | 8      |
|DEF  | 0      |
|XYZ  | 14     |
+-----+--------+

Any help is much appreciated!

Answer 1

Your where condition that filters dates could be filtering the empty results from the join i.e. when DEF is 0.

You may consider filtering in the join on the orders table eg

$ordersAllProducts=Product::groupBy('products.SKU')
                          ->selectRaw('products.SKU,
                                      (CASE
                                           WHEN sum(orders.quantity)>1 THEN sum(orders.quantity)
                                           ELSE 0
                                       END) as quantity_sum')
                 
                          ->leftjoin('orders',function($ordersJoin) use($from,$toTime){
                              $ordersJoin->on('products.SKU','=','orders.SKU');
                              $ordersJoin->whereBetween('date', [$from, $toTime->format('Y-m-d H:i:s')]);
                          })
                        ->get();

Let me know if this works for you.