Laravel's numerous unions
I am having a problem adding a query with multiple Unions "laravel way".
I am trying to execute a query equivalent to the one generated like this:
$ipsql = "";
for ($n = 1; $n < $total_networks; $n++) {
$ipsql .= "(SELECT * FROM ip WHERE network = " . $n . " AND used = 0 LIMIT 5)
UNION ALL";
}
if ($n == $total_networks) {
$ipsql .= "(SELECT * FROM ip WHERE network = " . $n . " AND used = 0 LIMIT 3) ORDER BY ip_addr";
}
I didn't find an option for joins with Eloquent, so I tried to use the query builder for this particular section, but I keep running into a problem when using the unionAll collector.
Using this:
$ip_list = DB::table('ips')->where('network', '=', '0')->where('used', '=', '0')->limit(5);
for($n = 1; $n < $network_count; $n++){
$ip_list = DB::table('ips')->where('network', '=', $n)->where('used', '=', '0')->limit(5)->unionAll($ip_list);
}
$ips = $ip_list->get();
I keep getting MySQL syntax error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near
'union all ((select * from `ips` where `network` = ? and `used` = ? limit 5) unio' at line 1
(SQL:
(select * from `ips` where `network` = 16 and `used` = 0 limit 5) union all ((select * from `ips`
where `network` = 15 and `used` = 0 limit 5) union all ((select * from `ips` where `network` = 14
and `used` = 0 limit 5) union all ((select * from `ips` where `network` = 13 and `used` = 0 limit 5)
union all ((select * from `ips` where `network` = 12 and `used` = 0 limit 5) union all ((select *
from `ips` where `network` = 11 and `used` = 0 limit 5) union all ((select * from `ips` where
`network` = 10 and `used` = 0 limit 5) union all ((select * from `ips` where `network` = 9 and
`used` = 0 limit 5) union all ((select * from `ips` where `network` = 8 and `used` = 0 limit 5)
union all ((select * from `ips` where `network` = 7 and `used` = 0 limit 5) union all ((select * from
`ips` where `network` = 6 and `used` = 0 limit 5) union all ((select * from `ips` where `network` =
5 and `used` = 0 limit 5) union all ((select * from `ips` where `network` = 4 and `used` = 0 limit
5) union all ((select * from `ips` where `network` = 3 and `used` = 0 limit 5) union all ((select *
from `ips` where `network` = 2 and `used` = 0 limit 5) union all ((select * from `ips` where
`network` = 1 and `used` = 0 limit 5) union all (select * from `ips` where `network` = 0 and `used`
= 0 limit 5)))))))))))))))))
I can see from the error that its nesting every new union that creates a syntax problem. I've tried the same task with DB :: raw, but it seems to be something similar too. Is there a way to achieve this that works better for laravel? Thanks for watching!
source to share
Your calls unionAll
do become nested. One solution is to create a subquery in a loop for
and then unionAll
that subquery to the main query after defining it. Then you will run get
throughout the shebang when you are done, e.g .:
$ips_list = DB::table('ips')->where('network', '=', '1')->where('used', '=', '0')->limit(5);
for($n = 1; $n < $total_networks; $n++){
$ip_list_subquery = DB::table('ips')
->where('network', '=', $n)
->where('used', '=', '0')
->limit(5);
$ips_list = $ips_list->unionAll($ip_list_subquery);
}
$ips = $ips_list->get();
This way you are chaining calls unionAll
:
$a->unionAll($b)->unionAll($c)->unionAll($d)...
instead of nesting them:
$a->unionAll($b->unionAll($c->unionAll($d...))))
source to share
// Use PDO. This is the simplest answer I found after two days of struggling to use a complex UNION in Laravel
$PDO = DB::connection('mysql')->getPdo();
$billingStmt = $PDO->prepare("
select * from (SELECT *
FROM t_statements
WHERE reference_id = $user_id
AND service_provider='FOLDER'
AND bill_name IS NOT NULL
ORDER BY bill_name ASC ) AS a
UNION ALL
SELECT *
FROM (
SELECT *
FROM t_statements
WHERE reference_id = $user_id
AND service_provider !='FOLDER'
AND bill_name IS NOT NULL
ORDER BY (CASE WHEN is_paid = 0 THEN due_date ELSE is_paid END) DESC) b
");
$billingStmt->execute();
$usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
header('Content-Type: application/json');
$androidUserBills = json_encode($usersBills); // return results as json
return response($androidUserBills);
// JSON response
[
{
"id": "247",
"created_at": "2016-02-23 10:44:33",
"updated_at": "2016-02-23 16:58:57",
"t_user_account_id": "245",
"statement_date": null,
"due_date": "0000-00-00 00:00:00",
"amount": "0",
"is_paid": "0",
"is_reminded": "1",
"overdue": null,
"current_amount": null,
"bill_total": "88.5",
"bill_id": "zd91NwGU",
"bill_name": "Utility",
"predecessor": null,
"reference_id": "120",
"service_provider": "FOLDER",
"sp_id": null
},
{
"id": "252",
"created_at": "2016-02-23 16:29:50",
"updated_at": "2016-02-23 16:58:25",
"t_user_account_id": "250",
"statement_date": null,
"due_date": "2016-03-04 17:52:34",
"amount": "0",
"is_paid": "0",
"is_reminded": "1",
"overdue": null,
"current_amount": null,
"bill_total": "88.5",
"bill_id": "Lojnc",
"bill_name": "Water bill",
"predecessor": null,
"reference_id": "120",
"service_provider": "IWK",
"sp_id": "7"
}
]
source to share