Hey, try with whereDate instead of where when comparing dates.
Dec 24, 2018
3
Level 1
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))
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.