我需要使用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 ((
,我不知道如何添加额外的´(`。
在您的情况下,您不需要使用子表,只需使用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);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句