DB::raw() in whereDate() is also applied to following where clauses

[dattz]

• Laravel Version: 5.4
• PHP Version: 7.0.8
• Database Driver & Version: MySQL 5.7.20

Description:

If DB::raw() is used in whereDate(), the following where clauses also get the same value supplied to DB::raw().

Steps To Reproduce:

1. Create an Eloquent which has at least two columns and one of them is a timestamp (I created a ordered_on timestamp field and status varchar field).
2. Build a query that uses DB::raw() in whereDate() and uses another where clause followed by it (The order matters. First use the, whereDate(), then use the other where method). Example:
   Orders::whereDate('ordered_on', '>', \DB::raw('CURDATE()')->where('status', 'Complete');
3. Notice the query executed - It should be:
   select * from 'orders' where date('ordered_on') >= CURDATE() and 'status' = 'CURDATE()'

Notice the 'status' = 'CURDATE()'.

[laurencei]

does it occur in 5.5?

[davidcb]

I have tested it on 5.5.21 and it still occurs.

$accounts = Account::whereDate('created_at', '>', \DB::raw('CURDATE()'))->where('name', 'Complete')->get();

generates the next query string:

select * from `accounts` where date(`created_at`) > CURDATE() and `name` = 'CURDATE()'

[srmklive]

If you do it like this:

$accounts = Account::whereDate('created_at', '>', 'CURDATE()')->where('name', 'Complete')->get();

Does it throws an error or works exactly like you expected?

[davidcb]

With that code it works:

select * from `accounts` where date(`created_at`) > 'CURDATE()' and `name` = 'Complete'

So the problem seems to be only with the method DB::raw in whereDate, as @dattz pointed.

[Miguel-Serejo]

It looks like the value is bound twice, as any subsequent where clause bindings will be shifted over, rather than replaced:

App\User::whereDate('created_at', '<', \DB::raw('CURDATE()'))->where('name', 'John Doe')->where('email', 'john@example.com')->get();

results in:

select * from `users` where date(`created_at`) < CURDATE() and `name` = 'CURDATE()' and `email` = 'John Doe';

As a workaround, consume the extra bound value in a way that won't alter your query, such as:

App\User::whereDate('created_at', '<', \DB::raw('CURDATE()'))->whereRaw('? is not null')->where('name', 'John Doe')->get();

which results in something like:

select * from `users` where date(`created_at`) < CURDATE() and 'CURDATE()' is not null and `name` = 'John Doe';

[srmklive]

Honestly i wouldn't use DB::raw() for using native MySQL functions for a value, when i can simply use it inside a string. `You could write the code as mentioned by @dattz like this:

$accounts = Account::whereRaw('created_at > CURDATE())->where('name', 'Complete')->get();

[Miguel-Serejo]

@srmklive Slight correction, you forgot to add the date part of the query (whereRaw('date(created_at)(...)), and that's important because what you wrote would be equivalent to using where() which does not suffer from this bug.

I guess turning the whereDate into a whereRaw is probably a more sensible workaround than consuming the extra binding.

In any case, this is still a bug in the framework that should be fixed.

[brechtdedecker]

Hmm I'm doing this query :
Order::whereDate('ordered_on', '>', \DB::raw('CURDATE()'))->where('status', 'Completed');

and my sql script looks like this : "select * from orders where date(ordered_on) > CURDATE() and status = ?"

[brechtdedecker]

I would suggest you using the Carbon library to get the current date.

[brechtdedecker]

executing :

Orders::whereDate('ordered_on', '>', \DB::raw('CURDATE()'))->where('status', 'Complete')->get();

generates the following input in the querylog:

array:1 [▼
0 => array:3 [▼
"query" => "select * from orders where date(ordered_on) > CURDATE() and status = ?"
"bindings" => array:2 [▼
0 => Expression {#113 ▼
#value: "CURDATE()"
}
1 => "Complete"
]
"time" => 2.45
]
]

Is this kinda what you we're looking for ?

[brechtdedecker]

If the bug is not already fixed, I would like to try and solve it, could anyone confirm that this issue is still open ?

[brechtdedecker]

Well using the whereDate seems to be the issue, cause when using a where clause like this:

Orders::where('ordered_on', '<', \DB::raw('CURDATE()'))->where('status', 'Complete')->get();

dd(DB::connection()->getQueryLog());

->

array:1 [▼
0 => array:3 [▼
"query" => "select * from orders where ordered_on < CURDATE() and status = ?"
"bindings" => array:1 [▼
0 => "Complete"
]
"time" => 2.11
]
]

seems to work fine.