我需要使用 Eloquent但不使用該 DB::table函數(shù)來執(zhí)行以下查詢。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這兩個(gè)結(jié)果，但我無法按別名a或limit結(jié)果排序。話雖這么說，這有效：$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();并返回錯(cuò)誤：方法 Illuminate\Database\Eloquent\Collection::getBindings 不存在。我還嘗試在函數(shù)內(nèi)部構(gòu)建查詢并且?guī)缀蹩梢哉９ぷ?，缺?的只是將我與目標(biāo)分開。$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 (，它應(yīng)該是a.type FROM ((，我不知道如何添加額外的 '(`。
1 回答
狐的傳說
TA貢獻(xiàn)1804條經(jīng)驗(yàn) 獲得超3個(gè)贊
對于雄辯的建設(shè)者
在您的情況下，您不需要使用子表，只需使用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);
- 1 回答
- 0 關(guān)注
- 179 瀏覽
添加回答
舉報(bào)
0/150
提交
取消