Support the ongoing development of Laravel.io →
Database Eloquent

Consider the following simplified tables:

https://gist.github.com/exnor/cff79953c6dace6c042c

  • 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.

Last updated 3 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

exnor exnor Joined 25 Feb 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.