Laravel多个联盟 [英] Laravel Multiple Unions

问题描述

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

I'm having an issue adding a query with multiple Unions the "laravel way".

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

The I am trying to accomplish a query equivalent to the one generated by the following:

$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时，我一直遇到问题.

I haven't found an option for unions with Eloquent so I was attempting to use the query builder for this particular section but I keep running into an issue while using the builders 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语法错误:

I keep getting a 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)))))))))))))))))

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

I can see from the error that its nesting each new union call which is creating the syntax issue. I tried accomplishing the same task with DB::raw but seem to be goofing that somewhere also. Is there a way to accomplish this thats better suited to laravel? Thanks for looking!

推荐答案

您的unionAll调用确实正在嵌套.一种解决方案是在for循环中创建一个子查询，然后将该子查询unionAll定义到主查询 之后.然后，在完成后在整个shebang上运行get，就像这样:

Your unionAll calls are indeed getting nested. One solution is to create a subquery in the for loop, and then unionAll that subquery to the main query after it's been defined. Then you run get on the whole shebang when you're done, like so:

$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调用:

So, effectively, you're chaining the unionAll calls:

$a->unionAll($b)->unionAll($c)->unionAll($d)...

而不是嵌套它们:

$a->unionAll($b->unionAll($c->unionAll($d...))))

这篇关于Laravel多个联盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！