Can't get any result with laravel query builder

Question

What is the different between laravel query builder

//fecth query 
$query = DB::table('order_details')
->select('order_code', 'product_name', 'qty', 'order_details.total_price as subtotal')
->leftJoin('orders', 'order_details.order_id', '=', 'orders.id')
->whereRaw('DATE_FORMAT(orders.order_date, "%Y-%m-%d") BETWEEN ? AND ?', ["'".$start_date."'", "'".$end_date."'"]);
var_dump($query->get());

and this mysql query

 select `order_code`, `product_name`, `qty`, `order_details`.`total_price`as `subtotal` 
 from `order_details` 
 left join `orders` on `order_details`.`order_id`=`orders`.`id` 
 where DATE_FORMAT(orders.order_date, "%Y-%m-%d") between '2017-07-01' and '2017-07-31'

I can't get any result from laravel query builder but I did on the mysql even though I have the same start and end date input. Is there anything wrong with my laravel query?

Edit : Hmmmm.... somehow after I changed the where clause to this

->whereRaw(DB::raw('DATE_FORMAT(orders.order_date, "%Y-%m-%d") BETWEEN "'.$start_date.'" AND "'.$end_date.'"'));

it gives me the same result as the mysql query, but why though. Do I use the whereRaw() correctly for the first where clause? It didn't give me the result if I'm using this whereBetween() too

->whereBetween(DB::raw('DATE_FORMAT(orders.order_date, "%Y-%m-%d")'), ["'".$start_date."'", "'".$end_date."'"]);

Do I make a mistake in the first whereRaw() and the whereBetween() usage and where is the mistake? Do whereRaw() can only pass array of integer? When I tried to see the query log on first whereRaw() and the whereBetween() using this code

DB::enableQueryLog();
var_dump($query->get());    
var_dump(DB::getQueryLog()[0]['query']);

I get this result

"select `order_code`, `product_name`, `qty`, `order_details`.`total_price` as `subtotal` 
from `order_details` 
left join `orders` on `order_details`.`order_id` = `orders`.`id` 
where DATE_FORMAT(orders.order_date, "%Y-%m-%d") BETWEEN ? AND ?"

It seems the '?' value wasn't replaced by $start_date and $end_date value.

Jason Jason · Accepted Answer · 2018-01-11T20:40:40

Try removing the quotes from your bindings in the whereRaw

->whereRaw('DATE_FORMAT(orders.order_date, "%Y-%m-%d") BETWEEN ? AND ?', [$start_date, $end_date]);

Also, as a recommendation, there is probably no need to use DATE_FORMAT either. If you are trying to do that date comparison where you only have the Date part , just alter your bindings:

    ->whereRaw('orders.order_date BETWEEN ? AND ?', 
         [$start_date . ' 00:00:00', $end_date . ' 23:59:59']]);

Can't get any result with laravel query builder

1 Answers