帶有非功能性約束的查詢以展示其意圖
public function readSongsVersions()
{
$data = Hierarchy::query()
->whereNull('parent_id')
->with(['children.song' => function ($query) {
$query->groupBy('number')
->orderBy('id', 'asc')
->max('id') //this does not work
}])
->get();
}
歌曲表
id version number
1 AAA 1
2 BBB 1
3 CCC 1
4 DDD 2
5 EEE 3
6 FFF 4
7 GGG 4
目標是只獲取每首歌曲的最新版本
歌曲編號 1 有 3 個版本，最后一個版本是：id:3 with version:CCC
第 4 首歌曲有 2 個版本，最后一首是：id:7 with version:GGG
預期的歌曲版本結果
CCC
DDD
EEE
GGG
uj5u.com熱心網友回復：
您可以嘗試使用子查詢來實作所需的輸出
//Assuming `child_id` is the foreign key on `songs` table
//which references primary key `id` on `children` table
public function readSongsVersions()
{
$data = Hierarchy::query()
->whereNull('parent_id')
->with([
'children.songs' => function ($query) {
$query->where('id', '=', function($query) {
$query->select(DB::raw('MAX(i.id)'))
->from('songs as i')
->whereRaw('i.number = songs.number')
->whereRaw('i.child_id = songs.child_id');
});
}
])
->get();
}
或者如果你想在沒有子查詢的情況下這樣做
//Assuming `child_id` is the foreign key on `songs` table
//which references primary key `id` on `children` table
public function readSongsVersions()
{
//Get ids of songs which have the highest id for particular number and child_id
//Means get the id of latest version of song for particular number
//within records of songs which belong to a particular children record
$ids = Song::query()
->select(DB::raw('MAX(id) as id'), 'child_id', 'number')
->groupBy('child_id', 'number')
->get()
->pluck('id');
//Then use these $ids to filter out the songs records
$data = Hierarchy::query()
->whereNull('parent_id')
->with([
'children.songs' => fn($query) => $query->whereIn('id', $ids)
])
->get();
}
uj5u.com熱心網友回復：
我想知道這是否適用于您的情況。
$data = Hierarchy::select("version","number")
->whereNull('parent_id')
->withMax(children.song','id')
->get()
->toArray();
你的陣列中應該有類似 song_version_max_id 的東西。或類似的，使用 dd($data) 查看結果。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/486460.html