Laravel：急切加载数据透视表并在数据透视表字段上排序

Question

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

Answer 1

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.