Расскажу, как отсортировать запросы к базе данных по полю Eloquent-отношения. Например, нам нужно сделать сортировку пользователей по названию их компании, которое находится в отдельной таблице companies. Сам способ сортировки зависит от типа отношений, и, по сравнению с обычной сортировкой, может быть довольно сложен.
В уроке будут рассмотрены следующие типы отношений:
- hasOne
- belongsTo
- hasMany
- belongsToMany
Сортировка отношений
Уточню, что мы пытаемся отсортировать запрос к базе данных 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.
Далее мы джойним таблицу companies, где user_id компании равен id пользователя.
И наконец, мы сортируем записи по полю 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.
Затем мы фильтруем результаты, сопоставляя user_id компании с 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.
Далее мы джойним таблицу companies, где id компании равен company_id пользователя.
И наконец, мы сортируем записи по полю 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.
Затем мы фильтруем результаты, сопоставляя id компании с 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.
Затем мы фильтруем результаты, сопоставляя user_id логина с id пользователя в родительском запросе.
Затем мы вызываем метод latest(), чтобы отсортировать логины до самой последней записи.
И, наконец, мы лимитируем результаты до одного, поскольку подзапрос может вернуть только одну строку и поле, хотя пользователь скорее всего будет иметь более одной даты логина.
Вот сгенерированный 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.
Далее мы джойним таблицу logins, где user_id логина равен id пользователя.
Далее мы группируем пользователей по их 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() для сортировки по алфавиту.
Вот удобная подсказка, которую я использую, чтобы запомнить, в каком порядке выполняются запросы.
Так, и какой из этих двух способов самый быстрый? 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, так как по нему мы хотим сортировать.
Затем мы джойним таблицу checkouts, с условием user_id равен id читателя. Нам это нужно, так как эта таблица соединяет books с users.
Далее, мы фильтруем результаты сопоставляя идентификатор оформленной книги book_id с id книги.
Затем мы сортируем оформление по полю borrowed_date, чтобы получить последнюю дату.
И, наконец, мы берем первую запись, поскольку подзапрос может вернуть только одну строку и поле, ну и потому, что нас интересует только последнее оформление.
И это всё, что нужно сделать! Вот сгенерированный 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');
});