Query HasManyThrough Laravel relation retrieving related records

Question

Considering the basic example Has Many Through relationship in the Laravel guide, I'm trying to query directly with eloquent, countries with posts from type 100 (just an example).

countries
    id - integer
    name - string

users
    id - integer
    country_id - integer
    name - string

posts
    id - integer
    user_id - integer
    title - string
    type_id - integer

type_id in posts is my peculiar case..

So, I created a relationship in the country model that creates a relation with posts through users. The result that I want to achieve is a list of countries and within each country the posts created by users of that country.

//Country.php
public function posts()
{
    return $this->hasManyThrough(
        'Post', 'User', 'country_id', 'user_id'
    );
}

I was able to replicate the result I want to achieve in other ways, but I would like to know why the eloquent query below does not return the expected result:

   $postsGroupedByCountries = Country::with([
        'posts' => function ($query) {
            $query->where('type_id', 100);
        }
    ])->get();

The return of the query are several countries with all posts without data. What is the correct way to do this query with Laravel Eloquent?

First try:

$postsGroupedByCountries = Country::whereHas('posts', function ($query) {
    $query->where('type_id', 100);
})->get();

Second try:

$postsGroupedByCountries = Country::whereHas('posts', function ($query) {
    $query->where('type_id', 100)->groupBy('posts.id');
})->get();

Third try:

$postsGroupedByCountries = Country::whereHas('posts', function ($query) {
    $query->where('type_id', 100);
})->get();

$postsGroupedByCountries = $postsGroupedByCountries ->map(function($country){
     $country->load('posts');
     return $country;
});

And I've already tried inside the load to use a closure with where as well. But it also did not work. The result is the same.

score 4 · Accepted Answer · answered Jun 20 '17 at 16:43

You have to use with and whereHas for the query. whereHas is going to constrain the Country query to only show countries with posts from the specified id but does not load those posts in the results. with() will then eager load the posts in the results BUT since it is called after the first query executes it does not know about the whereHas constraint and since it has been passed no constraints it will load all of the posts for each country. To load the Country's posts with the same constraints you would need to pass that to the with method as well.

All put together it would look like:

$countries = Country::whereHas('posts', function($query) {
    return $query->where('type_id', 100);
})
->with(['posts' => function($query) {
    return $query->where('type_id', 100);
}])
->get();

score 1 · Answer 2 · answered Jun 15 '17 at 23:52

1

You are eager loading all the posts with every country, that is why you're seeing them all. I would try modifying the query to this:

$postsGroupedByCountries = Country::whereHas('posts', function ($query) {
    $query->where('type_id', 100)
})->get();

See what that gets you returned.

answered Jun 15 '17 at 23:52

But I want to provide data with the posts loaded. The whereHas will do it? I think that whereHas is only to condition. – Rafael Soufraz Jun 16 '17 at 00:28
I think it will return you the loaded posts, give it a try and see what the results are. I have been referencing this post, btw: https://stackoverflow.com/questions/30231862/laravel-eloquent-has-with-wherehas-what-do-they-mean – Jun 16 '17 at 00:32
The posts wasn't loaded. I guess that with where has is lazy loading. I need to make a foreach after the eloquent query and call $country->posts to get it and put inside country. – Rafael Soufraz Jun 16 '17 at 13:09
After using whereas, if I try to access its relations (`$country->posts`) I get the same result as my first attempt. Lots of disconnected posts. – Rafael Soufraz Jun 19 '17 at 21:37
Try adding a groupBy to the nested query like :`$query->where('type_id', 100)->groupBy('posts.id')` – Jun 19 '17 at 21:41
Doesn't works to. Is it the case that I'm trying to do it in a way that would not be a 'good way'? I want to get Countries with Posts through a third table. Would not it be better to query entity entity and then join? Does Laravel solve my need with just one query? – Rafael Soufraz Jun 19 '17 at 21:44
I'll put my tries at the end of my post. – Rafael Soufraz Jun 19 '17 at 21:45

SAKIB · Answer 3 · 2022-08-16T11:56:13.050

0

// Country.php
public function postsOfTypeHundred()
{
    return $this->hasManyThrough('Post', 'User', 'country_id', 'user_id')
        ->where('type_id', 100);
}

/**
 * Laravel ^9.15
 * In Controller 
 */
$countries = Country::withWhereHas('postsOfTypeHundred')->get();

/** Created By */
$countries[0]->posts[0]->user->name

edited Aug 16 '22 at 11:56

answered Mar 03 '22 at 10:49

SAKIB

475
5
7

Query HasManyThrough Laravel relation retrieving related records

3 Answers3