Laravel Eloquent - nested relationship with constraint from two levels up

Question

** Updated post: I think this is actually impossible using Eloquent, so I have taken a different approach, thanks anyway! **

Consider the following simplified tables:

• content

id	name
1	news
2	review

• games

id	name
8	halo
9	gta

• releases

id	name	game_id
14	halo for ps3	8
15	halo for wii	8
16	gta for ps4	9

• content_releases

content_id	release_id	game_id
1	14	8
1	15	8
2	15	8
2	16	9

• Many content items can be linked to many releases via the content_games table.
• Many releases belong to one game
• As such, by linking content items to releases they are also inherently linked to games.

My models look like this:

class Content extends \Eloquent
{
    public function games()
    {
        return $this->belongsToMany('Models\Game', 'content_game');
    }
}

class Game extends \Eloquent
{
    public function releases()
    {
        return $this->belongsToMany('Models\Release', 'content_game');
    }
}

class Release extends \Eloquent 
{
    public function content()
    {
        return $this->belongsToMany('Models\Content', 'content_game');
    }
}

I am trying to write a relatively quick lazy-loaded eloquent query that can get me content, with related games nested under that, and then related releases under that. So I want the output array to look something like this:

Array
(
    [0] => Array
    (
        [id] => 1
        [name] => News
        [games] => Array
        (
            [0] => Array
            (
                [id] => 8
                [name] => Halo
                [releases] => Array
                (
                    [0] => Array
                    (
                        [id] => 14
                        [name] => Halo for PS3
                    )
                    [1] => Array
                    (
                        [id] => 15
                        [name] => Halo for Wii
                    )
                )
            )
        )
    )
    [1] => Array
    (
        [id] => 2
        [name] => Review
        [games] => Array
        (
            [0] => Array
            (
                [id] => 8
                [name] => Halo
                [releases] => Array
                (
                    [0] => Array
                    (
                        [id] => 15
                        [name] => Halo for Wii
                    )
                )
            )
            [1] => Array
            (
                [id] => 9
                [name] => GTA
                [releases] => Array
                (
                    [0] => Array
                    (
                        [id] => 16
                        [name] => GTA for PS4
                    )
                )
            )
        )
    )

)

Content::with('games', 'games.releases')->toArray(); does not work sufficiently with the 3 column table content_releases joining them all together, as Eloquent only queries based on two of the columns. As such I need to add a constraint games.releases.

It works (code below), but it's not at all efficient:

Content::with(['games' => function ($query) {
                        $query->groupBy(['game_id','game.id','pivot_content_id']);
                     },
                    'games.releases' => function ($query) {
                        $query->has('content');
                    }]);

It produces the following queries:

select * from "content" limit 10 offset 0

select "game".*, "content_game"."content_id" as "pivot_content_id", "content_game"."game_id" as "pivot_game_id" 
from "game" 
inner join "content_game" on "game"."id" = "content_game"."game_id"
where "content_game"."content_id" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)           
group by "game_id", "game"."id", "pivot_content_id"

select "releases".*, "content_game"."game_id" as "pivot_game_id", "content_game"."release_id" as "pivot_release_id"
from "releases" 
inner join "content_game" on "releases"."id" = "content_game"."release_id" 
where "content_game"."game_id" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
and (
    select count(*)
    from "content"
    inner join "content_game" on "content"."id" = "content_game"."content_id" 
    where "content_game"."release_id" = "releases"."id"
) >= 1

Can anyone suggest a more efficient way to approach this with the same output?

Thanks.

Answer 1

yeah, this question is a little bit dated but I struggled with this myself a while ago and a proper answer might be useful to others :)

There are a few issues with the way you setup your database tables and relationships so lets get them out of the way first:

If I understand correctly, there is a many-to-many relationship between content and games, and a one-to-many relationship between games and releases. Therefor you would create a pivot table for the relationship between content and games and use a foreign key on the releases table. This means renaming the pivot (for clarity) and dropping the release_id column.

Once that is done we have to update the model relationships. Content belongsToMany Game, Game belongsToMany Content and Game hasMany Release.

Now it is just a matter of using nested eloquent relationships, something that might have been added to the documentation since then and.. we are done.

Content::with(['games', 'games.releases'])->get()->toArray();

Answer 2

Try this..

Content::with('games')->with('releases')->with('content')->get()->toArray();