Consider the following simplified tables:
https://gist.github.com/exnor/cff79953c6dace6c042c
content
items can be linked to many releases
via the content_games
table.releases
belong to one game
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.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community