Laravel Eloquent - nested relationship with constraint from two levels up

eloquent, laravel, php

Solution

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

Problem

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

Original source