Been scratching my head over some queries not coming back with all the rows they should be, and I believe the reason is the bind variables not being ordered correctly. Is this a fluent bug, or am I doing something very wrong?
Here is an example, stripped down bare, to show what is happening. Query q1 selects from a table with a simple where condition. Query q2 joins to a table (kind of) with a condition in the ON statement. The main query q joins to a table with an arbitrary condition.
$q1 = DB::table('c')->where('d', '=', 'second');
$q2 = DB::table('e')->join('f', function($join){$join->where('f.id', '=', 'third');});
$q = DB::table('x')->join('y', function($join){$join->where('y.id', '=', 'first');})
->unionAll($q1) // binds to 'second'
->unionAll($q2); // binds to 'third'
var_dump($q->toSql());
var_dump($q->getBindings());
When this is run, this is the query and bind array that Fluent (in Laravel 4.2) generates:
(select * from `x` inner join `y` on `y`.`id` = ?)
union all
(select * from `c` where `d` = ?)
union all
(select * from `e` inner join `f` on `f`.`id` = ?)
array(3) {
[0]=>
string(2) "first"
[1]=>
string(2) "third"
[2]=>
string(2) "second"
}
Assuming the bind variables are matched in order, from start to finish, the bind variables for the second and third queries are the wrong way around, e.g. (select * fromcwhered= 'f1') which should be (select * fromcwhered= 'd1').
It seems that the where clause of q1 gets put at the end of the bind variable array, regardless of how many additional queries are subsequently added to the union. Or maybe that is just what this simplistic example looks. Perhaps the bind variables are not supposed to be in the order I think they should be?
Laravel passes the above query and bind array straight to PDO with no further processing.
The answer to my question is "yes". Fluent is mixing the bind variables up between UNION queries, so the final bind variable array is in the wrong order.
The Laravel query builder keeps its bind variables for each section of a query in an array:
// Illuminate\Database\Query\Builder
protected $bindings = array(
'select' => [],
'join' => [],
'where' => [],
'having' => [],
'order' => [],
);
That way you can build up the query in any order you like - SELECT first, WHERE first, or mix it up a little. That is great for a single query.
What Laravel then does, when building unions, is to merge together these arrays from each query in the union. That is where the bind variables get mixed up. Instead it should parse out the bind variable array for each query, into single (linear) arrays, and then concatenate these arrays in the order that it concatenates the union queries.
I have raised this as a bug. I can't see an easy fix, so have used a workaround: run each query separately, then merge the results together in PHP, rather than expecting the database to merge the results in the union query. Other solutions such as joining the query SQL and bindings together manually, and passing them direct to PDO would work, but I'm trying not to rewrite the logic within Laravel.
https://github.com/laravel/framework/issues/5833
My advice is not to use unions in Laravel 4.2, at least. If none of your queries have any bind variables, then you may be able to use it. However, do be aware that the query builder will use bind variables for any string, number, date, number or array that you pass into any part of a query.