我有以下原始 sql 查詢，它按預期作業
SELECT T1.category, T1.group, T1.series, T1.name, T2.cover
FROM (
SELECT category, MAX(COALESCE(image)) as cover
FROM stones
GROUP BY `category`
HAVING COUNT(DISTINCT category) = 1
) T2
INNER JOIN stones T1 on T2.category = T1.category;
在嘗試將其轉換為 Laravel Query Builder 語法時，我嘗試了以下操作
DB::table('stones', 't1')
->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
->join('stones as t2', 't2.category', '=', 't1.category')
->get();
它給出了一個錯誤
Illuminate\Database\QueryException with message '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 'FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join' at line 1 (SQL: select `t1`.`category`, `t1`.`group`, `t1`.`series`, `t1`.`name`, `t2`.`cover` from (SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join `stones` as `t2` on `t2`.`category` = `t1`.`category`)'
當我嘗試轉儲由 生成的 sql 時toSql()，我沒有看到與原始 sql 的任何區別，但是我無法發現錯誤。
# dump from toSql()
"select `t1`.`category`, `t1`.`group`, `t1`.`series`, `t1`.`name`, `t2`.`cover` from (SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join `stones` as `t2` on `t2`.`category` = `t1`.`category`"
uj5u.com熱心網友回復：
這可以通過直接選擇來完成，如下所示：
$results = DB::select('you full query');
dd($results);
這會容易得多。
有關更多資訊，請檢查此
您可以使用以下facade方法而不是構建雄辯的查詢：
DB::select用于選擇復雜查詢DB::statement用于 CRUD 查詢DB::insert直接用于完整插入查詢DB::update直接用于完整更新查詢DB::delete直接用于完全洗掉查詢
注意：如果需要，以上所有方法都應該接收引數系結
如果你不想系結引數使用 DB::unprepared('query')
uj5u.com熱心網友回復：
似乎在查詢構建器中，“cover”之后的“)”不應該存在，如果您比較輸出查詢和 toSql 溢位的查詢是差異。
uj5u.com熱心網友回復：
感謝@ChanYungKeat 和@adevel 指出更正“額外)”。去除和交換stones as t2與stones as t1中join()也可以正常作業。以下是供參考的作業查詢
DB::table('stones', 't1')
->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
->join('stones as t1', 't2.category', '=', 't1.category')
->get();
編輯：
我的用例需要按類別>組>系列對查詢結果進行分組，并準備資料進行序列化以供前端使用。所以我需要一個額外的步驟來映射每個結果條目并將其轉換為陣列（因為從查詢生成器回傳的 stdClass 物件可能無法正確序列化）
DB::table('stones', 't1')
->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
->join('stones as t1', 't2.category', '=', 't1.category')
->get()
/* Here I need to map over and convert the stdClass objects
* returned by Query Builder to array for proper serialization
*/
->map(function ($stone) {
return ['category' => $stone->category, 'group' => $stone->group, 'series' => $stone->series, 'name' => $stone->name, 'ccover' => $stone->cover];
})
->groupBy(['category', 'group', 'series'])
->toArray();
//OR using DB::select() as suggested by @Faesal
return collect(DB::select("SELECT t1.category, t1.group, t1.series, t1.name, t2.cover FROM stones as t1 JOIN (SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 ON t2.category = t1.category"))
/* Here I need to map over and convert the stdClass objects
* returned by Query Builder to array for proper serialization
*/
->map(function ($stone) {
return ['category' => $stone->category, 'group' => $stone->group, 'series' => $stone->series, 'name' => $stone->name, 'ccover' => $stone->cover];
})
->groupBy(['category', 'group', 'series'])
->toArray();
我還發現它可以使用 Eloquent（而不是 Query Builder）來完成。由于 Eloquent 回傳物件，Illuminate\Database\Eloquent\Model因此不需要額外的步驟來映射到陣列，因為序列化是由Model類處理的。
Stone::query()
->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
->join('stones as t1', 't2.category', '=', 't1.category')
->get()
->groupBy(['category', 'group', 'series'])
->toArray();
希望這可以幫助處于類似情況的人。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/383431.html
標籤：mysql 拉拉维尔 雄辩 laravel 查询构建器