嵌套约束groupBy加载并得到组的最后一项

Question

具有非功能约束的查询，以演示所要实现的内容。

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 版本:CCC

歌曲编号4有两个版本，最后一个版本是：id:7和版本:GGG

预期歌曲版本结果

 CCC
 DDD
 EEE
 GGG

Answer 1

您可以尝试使用子查询来实现所需的输出。

//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();
}

Answer 2

我想知道这在你的情况下是否有效。

$data = Hierarchy::select("version","number")
           ->whereNull('parent_id')
           ->withMax(children.song','id')
           ->get()
           ->toArray();

您应该在数组中包含类似song_version_max_id的内容。或者类似的，使用dd($data)查看结果。