Laravel eloquent bind same parameters to multiple use dont work in mysql

[julianphp]

I have a question, as I have a query like this

Job::whereRaw('IF (:duedatefrom IS NOT NULL AND :duedateto IS NOT NULL, duedate BETWEEN :duedatefrom AND :duedateto,1)',['duedatefrom' => $duedatefrom,'duedateto' => $duedateto])
    ->get();

I have two variables, duedatefrom and duedateto, and I use them in the query two times, then I get the error "SQLSTATE[HY093]: Invalid parameter number".

So what is the correct way to use a variable several times in the query, without having to duplicate the parameters?
Also, using with postgresql, that same query works perfectly and I can use :duedatefrom and :duedateto as many times as I want.

[dennisprudlo]

The keys you specified in the array doesn't matter because the query builder is only interested in the values:

framework/src/Illuminate/Database/Query/Builder.php

Lines 3262 to 3266 in 8e2d727

	$this->bindings[$type] = collect($this->bindings[$type])
	->merge($value)
	->map([$this, 'castBinding'])
	->values()
	->toArray();

Meaning you should pass a simple array with as many values as your query has parameters. The query builder will walk the query query and insert item by item. And because the name of the parameter is irrelevant you can even shorten the query by using ? instead of :somename:

Job::whereRaw(
    'IF (? IS NOT NULL AND ? IS NOT NULL, duedate BETWEEN ? AND ?, 1)',
    [ $duedatefrom, $duedateto, $duedatefrom, $duedateto ]
)->get();

[julianphp]

Ok, I understand, but I don't understand why it works with postgresql? And I prefer to put a name before ? because if not I think it's more complicated to understand when reading it and it's back to having to replicate the variables.

[henriquecm]

According to this stackoverflow answers you can't use the same parameter name more than once in a prepared statement, unless emulation mode is on.

https://stackoverflow.com/a/35375592/2015078

[julianphp]

Yes i see, and i dont touch the emulation mode. But it is curious that with postgresql if it works and not with mysql, maybe it changes the mode by itself.