Сортировка по полю отношений

Сортировка отношений в Laravel

Расскажу, как отсортировать запросы к базе данных по полю Eloquent-отношения. Например, нам нужно сделать сортировку пользователей по названию их компании, которое находится в отдельной таблицу companies. Сам способ сортировки зависит от типа отношений, и, по сравнению с обычной сортировкой, может быть довольно сложен.

В уроке будут рассмотрены следующие типы отношений:

Подробнее о типах отношений можно прочесть здесь: Просто и наглядно об Отношениях в Laravel.

Сортировка отношений

Уточню, что мы пытаемся отсортировать запрос к базе данных Eloquent-модели по значению одного из ее отношений. Мы не пытаемся просто сортировать результаты самих отношений. Фактически, вы можете отсортировать запрос к базе данных по полю отношения, даже не загружая его из базы данных!

Однако на тот случай, если вы попали в эту статью, пытаясь выяснить, как сортировать Eloquent-отношения, то вы можете воспользоваться тремя способами.

Во-первых, вы можете просто добавить оператор сортировки в сами отношения:

class Company extends Model
{
    public function users()
    {
        return $this->hasMany(User::class)->orderBy('name');
    }
}

Теперь, когда вы вызовите $company->users (как коллекцию) или $company->users() (как конструктор запросов), то пользователи будут автоматически отсортированы по их имени.

Второй способ это «жадная» загрузка (eager loading) отношений с оператором сортировки:

class CompaniesController extends Controller
{
    public function show(Company $company)
    {
        $company->load(['users' => function ($query) {
            $query->orderBy('name')
        }];

        return View::make('companies.show', ['company' => $company]);
    }
}

Такой подход позволяет нам на уровне контроллера задавать способы сортировки отношений в зависимости от ситуации.

Наконец, третий способ сортировки отношений — использование глобальных скоупов на самой модели отношений:

class User extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(fn ($query) => $query->orderBy('name'));
    }
}

Теперь, когда вы используете какой-либо User-запрос к базе данных, включая запросы на отношения, то он автоматически отсортирует результаты по имени пользователя.

Хорошо, хватит о сортировке самих отношений. Давайте теперь взглянем на сортировку родительской модели на основе поля отношений!

Сортировка отношений hasOne

Рассмотрим приложение, которое выводит список пользователей с именами, почтой и компанией. Сейчас список отсортирован по имени, а как его отсортировать по названию компании?

Список hasOne

В приложении есть модель User с отношением hasOne к компании. Это означает, что название компании лежит в таблице companies.

class User extends Model
{
    public function company()
    {
        return $this->hasOne(Company::class);
    }
}

Вообще есть два способа для сортировки пользователей по компании. Первый использует JOIN:

$users = User::select('users.*')
    ->join('companies', 'companies.user_id', '=', 'users.id')
    ->orderBy('companies.name')
    ->get();

Давайте разберем этот запрос.

Сначала мы берем только поля из таблицы users, поскольку по дефолту при JOIN Laravel берёт все поля, включая поля из таблицы companies.

User::select('users.*')

Далее мы джойним таблицу companies, где user_id компании равен id пользователя.

->join('companies', 'companies.user_id', '=', 'users.id')

И наконец, мы сортируем записи по полю name таблицы компании.

->orderBy('companies.name')

Вот сгенерированный SQL для этого запроса:

select users.*
from users
inner join companies on companies.user_id = users.id
order by companies.name asc

Второй способ — это использование подзапроса. Начиная с Laravel 6, методы orderBy() и orderByDesc() конструктора запросов поддерживают передачу запроса, а не просто названия полей. При этом запрос выполняется как подзапрос внутри оператора сортировки.

$users = User::orderBy(Company::select('name')
    ->whereColumn('companies.user_id', 'users.id')
)->get();

Опять же, давайте разберем этот запрос.

Сначала в методе orderBy() мы передаем подзапрос, который выбирает name из таблицы companies.

Company::select('name')

Затем мы фильтруем результаты, сопоставляя user_id компании с id пользователя.

->whereColumn('companies.user_id', 'users.id')

Вот сгенерированный SQL для этого запроса:

select * from users order by (
select name
from companies
where companies.user_id = users.id
) asc

И хотя этот второй способ тоже рабочий, но он значительно медленнее, чем способ с JOIN. Вот тесты с 50 000 пользователей:

Тест производительности отношения hasOne

Поэтому, при сортировке отношений hasOne, лучше джойнить.

Сортировка belongsTo

Практически всё тоже самое, что и сортировка hasOne, за исключением того, что внешние ключи находятся в противоположных таблицах. Чтобы сделать эту статью полезной с точки зрения документации, этот раздел будет почти полностью скопирован из инструкции по hasOne. Не стесняйтесь переходить к следующему разделу, если вы читаете эту статью полностью.

Рассмотрим приложение, которое выводит список пользователей с именами, почтой и компанией. Сейчас список отсортирован по имени, а как его отсортировать по названию компании?

Список belongsTo

В приложении есть модель User с отношением belongsTo к компании. Это означает, что название компании лежит в таблице companies.

class User extends Model
{
    public function company()
    {
        return $this->belongsTo(Company::class);
    }
}

Как и отношениях hasOne, мы можем использовать два способа для сортировки пользователей по названию их компании. Первый использует JOIN:

$users = User::select('users.*')
    ->join('companies', 'companies.id', '=', 'users.company_id')
    ->orderBy('companies.name')
    ->get();

Давайте разберем этот запрос.

Сначала мы берем только поля из таблицы users, поскольку по дефолту при JOIN Laravel берёт все поля, включая поля из таблицы companies.

User::select('users.*')

Далее мы джойним таблицу companies, где id компании равен company_id пользователя.

->join('companies', 'companies.id', '=', 'users.company_id')

И наконец, мы сортируем записи по полю name таблицы компании.

->orderBy('companies.name')

Вот сгенерированный SQL для этого запроса:

select users.*
from users
inner join companies on companies.id = users.company_id
order by companies.name asc

Второй способ — это использование подзапроса. Начиная с Laravel 6, методы orderBy() и orderByDesc() конструктора запросов поддерживают передачу запроса, а не просто названия полей. При этом запрос выполняется как подзапрос внутри оператора сортировки.

$users = User::orderBy(Company::select('name')
    ->whereColumn('companies.id', 'users.company_id')
)->get();

Опять же, давайте разберем этот запрос.

Сначала в методе orderBy() мы передаем подзапрос, который выбирает name из таблицы companies.

Company::select('name')

Затем мы фильтруем результаты, сопоставляя id компании с company_id пользователя.

->whereColumn('companies.id', 'users.company_id')

Вот сгенерированный SQL для этого запроса:

select * from users order by (
select name
from companies
where companies.id = users.company_id
) asc

И опять таки, как и у в отношениях hasOne, хотя второй способ тоже рабочий, но он значительно медленнее, чем способ с JOIN. Вот тесты с 50 000 пользователей:

Тест производительности отношений belongsTo

Поэтому, при сортировке отношений belongsTo, лучше джойнить.

Сортировка hasMany

Рассмотрим приложение, которое выводит список пользователей с именами, почтой и датой последнего входа. Сейчас список отсортирован по имени, а как его отсортировать по дате входа?

Список hasMany

(Если вам интересно, как получить дату последнего входа в систему самым эффективным способом, то обязательно посмотрите статью о динамических отношениях).

В приложении есть модель User с отношением hasMany к логинам. Это означает, что информация о логинах находится в таблице logins. Каждый раз, как пользователь входит в систему, то в этой таблице создается новая запись.

class User extends Model
{
    public function logins()
    {
        return $this->hasMany(Login::class);
    }
}

Есть два способа сортировки отношений hasMany. Можно сделать с помощью оператора JOIN или с помощью подзапроса. Давайте начнем с подзапроса, так как он проще.

Начиная с Laravel 6, методы orderBy() и orderByDesc() конструктора запросов поддерживают передачу запроса, а не просто названия полей. При этом запрос выполняется как подзапрос внутри оператора сортировки.

$users = User::orderByDesc(Login::select('created_at')
    ->whereColumn('logins.user_id', 'users.id')
    ->latest()
    ->take(1)
)->get();

Давайте повнимательнее посмотрим на этот подзапрос.

Сначала мы выбираем поле created_at из таблицы logins.

Login::select('created_at')

Затем мы фильтруем результаты, сопоставляя user_id логина с id пользователя в родительском запросе.

->whereColumn('logins.user_id', 'users.id')

Затем мы вызываем метод latest(), чтобы отсортировать логины до самой последней записи.

->latest()

И, наконец, мы лимитируем результаты до одного, поскольку подзапрос может вернуть только одну строку и поле, хотя пользователь скорее всего будет иметь более одной даты логина.

->take(1)

Вот сгенерированный SQL для этого запроса, который включает в себя подзапрос логина в операторе order by.

select * from users order by (
select created_at
from logins
where user_id = users.id
order by created_at desc
limit 1
) desc

Довольно часто я беру оператор order by и создаю для него скоуп в модели, просто для красивой инкапсуляции и удобного повторного использования. Например:

public function scopeOrderByLastLogin($query, $direction = 'desc')
{
    $query->orderBy(Login::select('created_at')
        ->whereColumn('logins.user_id', 'users.id')
        ->latest()
        ->take(1),
        $direction
    );
}

И теперь вы можете просто вызвать этот скоуп в контроллере (или там, где вам это нужно):

$users = User::orderByLastLogin()->get();

Хорошо, теперь давайте посмотрим на способ с JOIN.

$users = User::select('users.*')
    ->join('logins', 'logins.user_id', '=', 'users.id')
    ->groupBy('users.id')
    ->orderByRaw('max(logins.created_at) desc')
    ->get();

Давайте разберем этот запрос.

Сначала мы берем только поля из таблицы users, поскольку по дефолту при JOIN Laravel берёт все поля, включая поля из таблицы logins.

User::select('users.*')

Далее мы джойним таблицу logins, где user_id логина равен id пользователя.

->join('logins', 'logins.user_id', '=', 'users.id')

Далее мы группируем пользователей по их id, так как нам нужен только одна строка на одного пользователя.

->groupBy('users.id')

Наконец, и здесь всё становится немного интереснее, мы сортируем записи по max полю created_at, по убыванию, чтобы сначала получить последних вошедших пользователей.

Вот сгенерированный SQL для этого запроса:

select users.*
from users
inner join logins on logins.user_id = users.id
group by users.id
order by max(logins.created_at) desc

Возможно вы удивитесь, почему мы здесь используем агрегатную функцию max(). Разве нельяз просто отсортировать по полю created_at. Например, так:

->orderByDesc('logins.created_at')

Короткий ответ — нет.

Без этой агрегатной функции мы получим следующую синтаксическую ошибку:

Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘logins.created_at’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Что это значит?

Когда мы джойним таблицу логинов, то получаем по нескольку результатов на каждого пользователя. По строке для каждой записи логина, который они совершали.

А нам нужна только одна запись на пользователя, поэтому мы группируем их по id пользователя.

Однако, затем мы говорим MySQL остортировать эти сгруппированные строки по полю created_at. Но если на пользователя приходится несколько строк, то это означает, что у нас есть несколько разных значений created_at. Как MySQL узнает, по какому created_at нужно сортировать?

Вот она и не знает и мы получаем ошибку.

Здесь важно понять, что при выполнении запроса сортировка происходит после группировки. Это означает, что оператор order by выполняется на сгруппированных строках. И, как и в случае с оператором select, вы должны использовать агрегатную (group by) функцию, чтобы сказать MySQL, какое значение вы хотите использовать из этого группы.

И именно поэтому мы использовали агрегатную функцию max() в нашем запросе. Она говорит MySQL, что нам нужно самое последнее (максимальное) значение created_at из группы. И это работает и для других типов полей. Например, вы можете использовать функции max() и min() для сортировки по алфавиту. Полный список этих агрегатных функций смотрите в документации MySQL.

Вот удобная подсказка, которую я использую, чтобы запомнить, в каком порядке выполняются запросы.

Порядок Оператор Функция
1 from Выбор и объединение таблиц для получения данных
2 where Фильтрация данных
3 group by Агрегирование данных
4 having Фильтрация агрегированных данных
5 select Выбор данных для получения
6 order by Сортировка данных
7 limit/offset Ограничение данных определенными строками

Так, и какой из этих двух способов самый быстрый? JOIN или подзапрос? Вот тесты с 50 000 пользователей:

Тест производительности отношений hasMany

Как видите, победителя нет. Соответственно, при сортировке отношений hasMany я бы предложил попробовать оба способа, чтобы понять, какой из них лучше всего подходит для вашего конкретного случая.

Небольшое примечание: чтобы получить эти результаты нужно создать составной индекс в таблице logins для полей user_id и created_at.

Schema::table('logins', function (Blueprint $table) {
    $table->index(['user_id', 'created_at']);
});

Сортировка belongsToMany

Рассмотрим библиотечное приложение, которое выводит список с названием книги, её автором, последним читателем и датой оформления в читательском билете. Сейчас список отсортирован по названию книг, а как его отсортировать по дате оформления или же по имени читателя?

Список отношений belongs-to-many

В приложении есть модель Book с отношением belongsToMany к читателю, использующим сводную таблицу checkouts. Каждый раз, когда пользователь оформляет книгу, то в этой таблице создается новая запись, включая дату в поле borrowed_date.

class Book extends Model
{
    public function user()
    {
        return $this->belongsToMany(User::class, 'checkouts')
            ->using(Checkout::class)
            ->withPivot('borrowed_date');
    }
}

class Checkout extends Pivot
{
    protected $table = 'checkouts';

    protected $casts = [
        'borrowed_date' => 'date',
    ];
}

Начнем с сортировке по последней дате оформления (borrowed date).

На самом деле это просто, ведь borrowed_date есть в нашей сводной таблице checkouts. Оформление (checkouts) по сути имеет отношения hasMany к books. Одна книга отношение hasMany к оформлению. Мы просто используем checkouts в качестве сводной таблицы для отношений belongsToMany между books и users.

Это означает, что если мы хотим отсортировать по полю в таблице checkouts, то мы можем использовать те же самые методы, которые мы рассмотрели выше в разделе отношений hasMany .

Вот краткий обзор, как вы можете сделать это с помощью подзапроса:

$books = Books::orderByDesc(Checkout::select('borrowed_date')
    ->whereColumn('book_id', 'books.id')
    ->latest('borrowed_date')
    ->limit(1)
)->get();

Вы можете спросить: «А если я не использую сводную модель Checkout, то всё равно получится?»

Разумеется. Начиная с Laravel 6, вы можете использовать в методе сортировки замыкание, где вы можете разместить свой подзапрос.

$books = Books::orderByDesc(function ($query) {
    $query->select('borrowed_date')
        ->from('checkouts')
        ->whereColumn('book_id', 'books.id')
        ->latest('borrowed_date')
        ->limit(1);
})->get();

Вот сгенерированный SQL для обоих этих запросов (они идентичны):

select * from books order by (
select borrowed_date
from checkouts
where book_id = books.id
order by borrowed_date desc
limit 1
) desc

Хорошо, теперь давайте перейдем к фактической сортировке по полю отношений belongsToMany.

В нашем примере это означает сортировку по полю в таблице users. Давайте обновим запрос, чтобы отсортировать по имени пользователя, который последний раз оформлял эту книгу.

Опять же, для этого мы будем использовать подзапрос:

$books = Book::orderBy(User::select('name')
    ->join('checkouts', 'checkouts.user_id', '=', 'users.id')
    ->whereColumn('checkouts.book_id', 'books.id')
    ->latest('checkouts.borrowed_date')
    ->take(1)
)->get();

Давайте повнимательнее посмотрим на наш подзапрос.

Сначала мы выбираем name из таблицы users, так как по нему мы хотим сортировать.

User::select('name')

Затем мы джойним таблицу checkouts, с условием user_id равен id читателя. Нам это нужно, так как эта таблица соединяет books с users.

->join('checkouts', 'checkouts.user_id', '=', 'users.id')

Далее, мы фильтруем результаты сопоставляя идентификатор оформленной книги book_id с id книги.

->whereColumn('checkouts.book_id', 'books.id')

Затем мы сортируем оформление по полю borrowed_date, чтобы получить последнюю дату.

->latest('checkouts.borrowed_date')

И, наконец, мы берем первую запись, поскольку подзапрос может вернуть только одну строку и поле, ну и потому, что нас интересует только последнее оформление.

->take(1)

И это всё, что нужно сделать! Вот сгенерированный SQL для этого запроса:

select * from books order by (
select name
from users
inner join checkouts on checkouts.user_id = users.id
where checkouts.book_id = books.id
order by checkouts.borrowed_date desc
limit 1
) asc

Небольшое предупреждение насчёт производительности. Несмотря на то, что этот метод хорош в определенных ситуациях, но если вы имеете дело с десятками тысяч записей, то он, возможно, будет не очень быстрым. Так что, если начнутся проблемы с производительностью, то самое время заняться кэшированием.

И, кстати, я бы начал с добавления внешнего ключа last_checkout_id в таблицу books в качестве первого шага денормализации.

Schema::table('books', function (Blueprint $table) {
    $table->foreignId('last_checkout_id')->nullable()->constrained('checkouts');
});

Автор: Jonathan Reinink
Перевод: Алексей Широков

Наши Телеграм и Вконтакте — следите за выходом новых статей через наши каналы.

Задать вопросы по урокам можно на нашем форуме.