php - Laravel 4 union 生成错误的 SQL

Question

这是代码:

    $f = DB::table("topics")
           ->join("recommends", "topics.id", "=", "recommends.courseid")
           ->where("recommends.re_type", "=", $re_type)
           ->where("recommends.re_location", "=", $re_location)
           ->orderBy("recommends.weigh", "desc");

    $s = DB::table("topics")
           ->orderBy("topics.create_time", "desc");

    $f->union($s)->get();

我在关键字 union 周围得到了错误的 SQL:

    select * from `topics` inner join `recommends` 
    on `topics`.`id` = `recommends`.`courseid` 
    where `recommends`.`re_type` = ? 
    and `recommends`.`re_location` = ? 
    order by `recommends`.`weigh` desc 
    union //here!!!!!
    select * from `topics` order by `topics`.`create_time` desc

错误信息:

SQLSTATE[HY000]: General error: 1221
Incorrect usage of UNION and ORDER BY (SQL: ...)
(Bindings: array ( 0 => 3, 1 => 7, ))

问题是什么？

Answer 1

MySQL UNION 期望所有语句中具有相同的列。由于您要连接 $f 中的另一个表，因此两个语句之间的列不匹配。

参见MySql SELECT union for different columns?

在这种情况下，直接使用 PDO 对象可能不会那么麻烦。

$pdo = DB::connection()->getPdo();