I have a Countries table and a pivot table Country_language where all the countries are listed with their translation in the available languages.
The table structure is as follows :
Languages
--------------
ID
Locale
Records :
1 - EN
2 - FR
Countries
------------
ID
code
Records:
1 - BE
2 - US
Country_language
-----------------------
ID
country_id
language_id
name
Records :
1 - 1 - 1 - Belgium
2 - 1 - 2 - Belgique
3 - 2 - 1 - United States
4 - 2 - 3 - Les États-Unis
On my countries model I have a relationship with the language pivot table as follows :
public function translation()
{
return $this->belongsToMany('Language', 'country_language', 'country_id','language_id')->withPivot('name');
}
Now I would like to get a list of all the countries for a given language and this should be sorted e.g by name DESC.
I do this with the following code :
$countries = Country::with(array('translation' => function($query) {
$query->where('language_id', '=', 1); // fetch countries in English
$query->orderBy('name', 'desc');
}))->get();
If I print the $countries however I get the following list, which is NOT in the correct order. I expect it to give "United States" first and then "Belgium".
[
{
"id": 1,
"code": "BE",
"translation":
[{
"id": 1,
"locale": "EN",
"pivot":
{
"country_id": 1,
"language_id": 1,
"name": "Belgium"
}
}]
},
{
"id": 2,
"code": "US",
"translation":
[{
"id": 2,
"locale": "EN",
"pivot":
{
"country_id": 2,
"language_id": 1,
"name": "United States"
}
}]
}
]
If I check the query that's being run it looks as follows :
select `languages`.*, `country_language`.`country_id` as `pivot_country_id`, `country_language`.`language_id` as `pivot_language_id`,
`country_language`.`name` as `pivot_name` from `languages`
inner join `country_language` on `languages`.`id` = `country_language`.`language_id`
where `country_language`.`country_id` in (?, ?) and `language_id` = ? order by `name` desc
Which is correct and if I run this in MySQL I get the list of countries in descending name order.
Am I doing something wrong here or is this perhaps an issue in Laravel? Thanks for taking your time to answer.
EDIT : Basically I just want the following query in Eloquent.. Seems simple but apparently very hard (if possible) to accomplish in Eloquent :
select country_language.*, languages.*
from country_language
join countries on countries.id = country_language.country_id
join languages on languages.id = country_language.language_id
where country_language.language_id = 1
order by country_language.name desc
You are using eager loading constraints. This does not affect the outcome of your outer query - it only restricts and orders the related records. If you would, e.g. retrieve $countries->first()->translation -> then those are ordered by name.
In your case you would receive all countries, even if there is no record in the country_language table. Because your restrictions are only applied to the related records.
If you want only the countries that have a related record in the country_language table you should use whereHas. This could result in something like:
$countries = Country::with(array('translation' => function($query) {
$query->where('language_id', '=', 1); // fetch only country_language records with language_id = 1
}))->whereHas('translation', function($query) {
$query->where('language_id', '=', 1); // fetch countries in English
$query->orderBy('name', 'desc');
->get();
Please check if this will order your results in a correct way. I can imagine that the orderBy has to be chained on the original countries collection, therefore not within an inner function.
It should then be something like:
$countries = Country::with(array('translation' => function($query) {
$query->where('language_id', '=', 1); // fetch only country_language records with language_id = 1
}))->whereHas('translation', function($query) {
$query->where('language_id', '=', 1); // fetch countries in English
->orderBy('name', 'desc');
->get();
I am not sure if you can order on the name column in that case. Maybe it is not accessible , then you probably need to use a join statement.