Nested eager loading with constraint groupBy and get just last item of group-CodePudding

Query with non-functional constrain to demonstrate what is intended

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

songs table

   id  version  number
   1   AAA     1
   2   BBB     1
   3   CCC     1
   4   DDD     2
   5   EEE     3
   6   FFF     4
   7   GGG     4

The goal is to get only the latest version of each song

Song number 1 has 3 versions and de last one is: id:3 with version:CCC

Song number 4 has 2 versions and de last one is: id:7 with version:GGG

Expected songs versions result

 CCC
 DDD
 EEE
 GGG

CodePudding user response：

You could try to set the limit to 1 on the with subquery

public function readSongsVersions() 
{
   $data = Hierarchy::query()
           ->whereNull('parent_id')
           ->with(['children.song' => function ($query)  {
                    $query->groupBy('number')
                          ->latest('id')
                          ->limit(1) //try setting a limit
           }])
           ->get();
   }
}

Or you could define a relation on the Child (representing children) relation to get the latest song by id

//In the model for children relationship
public function latestSong()
{
    return $this->hasOne(Child::class)->latestOfMany();
}

Then in controller

public function readSongsVersions() 
{
   $data = Hierarchy::query()
           ->whereNull('parent_id')
           ->with('children.latestSong')
           ->get();
}

Laravel Docs - Eloquent Relationships - Has one of Many

CodePudding user response：

I wonder if this would work in your case.

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

You should have something like song_version_max_id into your array. or similar, use dd($data) to see results.