Rafael Soufraz Rafael Soufraz - 1 month ago 5
PHP Question

Query HasManyThrough Laravel relation retrieving related records

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.

Answer Source

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