php - Laravel多个联盟

我在以“laravel方式”添加具有多个联合的查询时遇到问题。

我正在尝试完成与以下命令生成的查询等效的查询:

$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";
}

我没有找到与Eloquent联合的选项，因此我试图在该特定部分使用查询构建器，但是在使用构建器unionAll时，我一直遇到问题。

使用这个:

$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();

我不断收到MySQL语法错误:

     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)))))))))))))))))

从错误中我可以看到，它嵌套了每个新的联合调用，从而创建了语法问题。
我试图用DB::raw完成相同的任务，但似乎也在某个地方犯了错误。有没有办法做到这一点更适合laravel？
感谢您的光临!

最佳答案

您的unionAll调用确实已嵌套。一种解决方案是在for循环中创建一个子查询，然后在定义主查询后将该子查询unionAll编码到主查询中。然后，在完成操作后，在整个shebang上运行get，如下所示:

$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();

因此，有效地，您正在链接unionAll调用:
$a->unionAll($b)->unionAll($c)->unionAll($d)...
而不是嵌套它们:
$a->unionAll($b->unionAll($c->unionAll($d...))))

关于php - Laravel多个联盟，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25924592/