Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mikail10000000's avatar

Where clause does not work in group by query builder

Hi, the query works fine without where clause, but currently it returns empty result



return DB::table('orders')
         ->select(
             DB::Raw('COUNT(orders.id) as order_count'),
             DB::Raw('DATE(orders.created_at) day')
        )
        ->where('created_at','>' , Carbon::parse($request->start))
        ->where('created_at ','<' ,Carbon::parse($request->end))
        ->groupBy('day')
        ->orderBy('orders.created_at')
        ->get();


toSql() result :

select COUNT(orders.id) as order_count, DATE(orders.created_at) day from "orders" where "created_at" > ? and "created_at " < ? group by "day" order by "orders"."created_at" asc

here is what the dates look like:

dd(Carbon::parse($request->start)); 

Outputs:

Carbon\Carbon @1538352000 {#1291
  date: 2018-10-01 00:00:00.0 UTC (+00:00)
} 

EDIT:

If I have only one where or two where clause where both operators are greater ">" I get the results from db:


    ->where('created_at','>' , Carbon::parse($request->start))
    ->where('created_at ','>' ,Carbon::parse($request->end))

0 likes
3 replies
manelgavalda's avatar

Hey, try with whereDate instead of where when comparing dates.

mikail10000000's avatar

@GLOBALS - Thank You but it produces the same outcome, here is some more info :

EDIT:

If I have only one where or two where clause where both operators are greater ">" I get the results from db:


    ->where('created_at','>' , Carbon::parse($request->start))
    ->where('created_at ','>' ,Carbon::parse($request->end))

manelgavalda's avatar
Level 50

@MIKAIL10000000 - On your second where you have an extra space after the created_at field name

    ->where('created_at','>' , Carbon::parse($request->start))
    ->where('created_at ','>' ,Carbon::parse($request->end)) // here

should be this:

    ->where('created_at','>' , Carbon::parse($request->start))
    ->where('created_at','>' ,Carbon::parse($request->end)) // here

I thought that it was a typo at first, but I see that in your ->toSql code it also shows this typo so maybe this is the problem. Other than that I think the rest is fine.

1 like

Please or to participate in this conversation.