使用 Eloquent 的 Laravel 联合查询

Question

我需要使用 Eloquent但不使用该 DB::table函数来执行以下查询。SELECT a.name, a.type FROM ((    SELECT name, 'Customer' as `type`    FROM customers)UNION ALL (    SELECT name, 'Supplier' as `type`    FROM suppliers)) AS a ORDER BY a.type ASC LIMIT 20到目前为止，我能够获得merge这两个结果，但我无法按别名a或limit结果排序。话虽这么说，这有效：$a = Customers::select('name', DB::raw("'Customer' AS `type`"))->get();$b = Suppliers::select('name', DB::raw("'Supplier' AS `type`"))->get();$result = $a->merge($b);但这并不$result = $a->merge($b)->limit(10);我还尝试fromSub按如下方式使用该功能：$result = Customers::selectRaw('a.name, a.type')->fromSub(function ($subquery) use ($a, $b) {    $subquery->union($a)->union($b);})->get();并返回错误：方法 Illuminate\Database\Eloquent\Collection::getBindings 不存在。我还尝试在函数内部构建查询并且几乎可以正常工作，缺少(的只是将我与目标分开。$result = Customers::selectRaw('a.name, a.type')->fromSub(function ($subquery) {    $sql = "name, 'Customer' AS type FROM customers) ";    $sql .= ' UNION ALL ';    $sql .= "(SELECT name, 'Supplier' AS type FROM suppliers) ";    $subquery->select(DB::raw($sql));}, 'a')->get();这将返回 SQL 查询：SELECT a.name, a.type FROM (SELECT name, 'Customer' AS type FROM customers) UNION ALL (SELECT name, 'Supplier' AS type FROM suppliers)) as `a`但不幸的是，它缺少(after a.type FROM (，它应该是a.type FROM ((，我不知道如何添加额外的 '(`。

Answer 1

对于雄辩的建设者

在您的情况下，您不需要使用子表，只需使用union 没有子表：

$a = Customers::select('name', DB::raw("'Customer' AS `type`"));

$b = Suppliers::select('name', DB::raw("'Supplier' AS `type`"));

$a->union($b)->orderBy('type')->limit(20);

如果你想使用subtable，你可以这样做：

$a = Customers::select('name', DB::raw("'Customer' AS `type`"));

$b = Suppliers::select('name', DB::raw("'Supplier' AS `type`"));

$c = $a->union($b);

Customers::selectRaw('a.name, a.type')

         ->from(DB::raw("(".$c->toSql().") AS a"))

         ->mergeBindings($c->getQuery()) // if you have parameters

         ->orderBy('type')

         ->limit(20);

对于查询生成器

你可以这样做：

$a = Customers::select('name', DB::raw("'Customer' AS `type`"));

$b = Suppliers::select('name', DB::raw("'Supplier' AS `type`"));

$c = $a->union($b);

DB::table(DB::raw("({$c->toSql()}) AS a"))

->mergeBindings($c->getQuery())

->orderBy('a.type')

->select('a.name', 'a.type')

->limit(20);