Расскажу, как отсортировать запросы к базе данных по полю 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
Рассмотрим приложение, которое выводит список пользователей с именами, почтой и компанией. Сейчас список отсортирован по имени, а как его отсортировать по названию компании?
В приложении есть модель 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, лучше джойнить.
Сортировка belongsTo
Практически всё тоже самое, что и сортировка hasOne, за исключением того, что внешние ключи находятся в противоположных таблицах. Чтобы сделать эту статью полезной с точки зрения документации, этот раздел будет почти полностью скопирован из инструкции по hasOne. Не стесняйтесь переходить к следующему разделу, если вы читаете эту статью полностью.
Рассмотрим приложение, которое выводит список пользователей с именами, почтой и компанией. Сейчас список отсортирован по имени, а как его отсортировать по названию компании?
В приложении есть модель 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, лучше джойнить.
Сортировка 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 я бы предложил попробовать оба способа, чтобы понять, какой из них лучше всего подходит для вашего конкретного случая.
Небольшое примечание: чтобы получить эти результаты нужно создать составной индекс в таблице logins для полей user_id и created_at.
Schema::table('logins', function (Blueprint $table) {
$table->index(['user_id', 'created_at']);
});
Сортировка belongsToMany
Рассмотрим библиотечное приложение, которое выводит список с названием книги, её автором, последним читателем и датой оформления в читательском билете. Сейчас список отсортирован по названию книг, а как его отсортировать по дате оформления или же по имени читателя?
В приложении есть модель 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
Перевод: Алексей Широков
Наш 
Телеграм-канал — следите за новостями о Laravel.