Динамические отношения в Laravel с использованием подзапросов

При создании веб-приложений, взаимодействующих с базой данных, у меня всегда есть две цели:

  • Сократить запросы к базе данных до минимума.
  • Сократить использование памяти до минимума.

Эти вещи могут оказать существенное влияние на производительность вашего приложения.

Разработчики, как правило, хорошо справляются с первой целью. Мы знаем о проблеме N + 1 и используем такие методы, как eager-loading для ограничения запросов к базе данных. Тем не менее, мы не всегда правильно используем память. На самом деле, мы иногда приносим больше вреда, чем пользы, пытаясь сократить запросы к базе данных за счет использования памяти.

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

Бросаем вызов

Рассмотрим следующий пример. У вас есть страница пользователей в вашем приложении, которая показывает некоторую информацию о них, включая дату последнего входа в систему. Эта, казалось бы, простая страница на самом деле представляет интересную сложность.

Имя Email Дата последнего входа
Иван Иванов [email protected] 10 ноября 2018
Петя Петичкин [email protected] 20 июня 2017
Вася Васечкин [email protected] 5 декабря 2018
Иван Васечкин [email protected] 21 мая 2017

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

Schema::create('users', function (Blueprint $table) {
  $table->increments('id');
  $table->string('name');
  $table->string('email');
  $table->timestamps();
});

Schema::create('logins', function (Blueprint $table) {
  $table->increments('id');
  $table->integer('user_id');
  $table->string('ip_address');
  $table->timestamp('created_at');
});

А это соответствующие модели для этих таблиц со своими отношениями:

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

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

Итак, как мы собираемся создать страницу пользователей выше? В частности, как мы получаем последнюю дату входа? Легкий ответ здесь может заключаться в следующем:

$users = User::all();

@foreach ($users as $user)
   <tr>
     <td>{{ $user->name }}</td>
     <td>{{ $user->email }}</td>
     <td>
        @if ($lastLogin = $user->logins()->latest()->first())
           {{ $lastLogin->created_at->format('M j, Y \a\t g:i a') }}
        @else
           Never
        @endif
     </td>
   </tr>
@endforeach

Но, если мы хороший разработчик (а мы именно такой), то мы увидим здесь проблему. Мы только что создали проблему N + 1. Для каждого пользователя, которого мы показываем, мы делаем дополнительный запрос, чтобы получить дату его последнего входа в систему. Если на нашей странице отображается 50 пользователей, то мы выполняем еще 51 дополнительных запросов к базе данных.

select * from "users";
select * from "logins" where "logins"."user_id" = 1 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 2 and "logins"."user_id" is not null order by "created_at" desc limit 1;
// ...
select * from "logins" where "logins"."user_id" = 49 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 50 and "logins"."user_id" is not null order by "created_at" desc limit 1;

Давайте проверим сможем ли мы сделать лучше. На этот раз загрузим все записи последнего входа:

$users = User::with('logins')->get();

@foreach ($users as $user)
  <tr>
    <td>{{ $user->name }}</td>
    <td>{{ $user->email }}</td>
    <td>
      @if ($user->logins->isNotEmpty())
        {{ $user->logins->sortByDesc('created_at')->first()->created_at->format('M j, Y \a\t g:i a') }}
      @else
        Never
      @endif
    </td>
  </tr>
@endforeach

Это решение требует всего двух запросов к базе данных. Один для выборки всех пользователей, а второй для соответствующих записей входа в систему. Успех!

Ну, не совсем. Теперь у нас появилась проблема с использованием памяти. Конечно, мы избежали проблемы с N + 1, но на самом деле мы создали большую проблему с памятью:

Проблема с использованием памяти в Laravel

Теперь мы загружаем 12 500 записей, просто чтобы показать дату последнего входа для каждого пользователя. Это будет не только потреблять память, но и потребует дополнительных вычислений, поскольку каждая запись должна быть инициализирована как модель Eloquent. И это еще довольно примитивный пример. Вы можете легко столкнуться с подобными ситуациями, которые приводят к загрузке миллионов загружаемых записей.

Кэширование

На этот момент вы, возможно, думаете, «неважно, я просто закэширую дату последнего входа».

Schema::create('users', function (Blueprint $table) {
  $table->integer('last_login_id');
});

Когда пользователь войдет в систему, мы создадим новую запись входа, а затем обновим внешний ключ last_login_id у пользователя. Затем мы создадим отношения lastLogin в нашей модели пользователей и просто будем загружать эту связь.

$users = User::with('lastLogin')->get();

Это абсолютно правильное решение. Но имейте в виду, что кэширование часто не так просто как кажется. Да, есть ситуации, когда денормализация уместна. Мне просто не нравится использовать этот подход только из-за ограничения в моем ORM. Мы можем сделать лучше.

Знакомьтесь, Подзапросы (Subqueries)

Существует еще один способ решить эту проблему, и связан он с использованием подзапросов. Подзапросы позволяют нам выбирать дополнительные столбцы (атрибуты) прямо в нашем основном запросе к базе данных (запрос пользователей в нашем примере). Давайте посмотрим, как мы можем это сделать. Вы можете быть удивлены, узнав, что Laravel фактически имеет встроенную поддержку подзапросов с использованием метода selectSub.

Сначала длинная версия:

$lastLogin = Login::select('created_at')
  ->whereColumn('user_id', 'users.id')
  ->latest()
  ->limit(1)
  ->getQuery();

$users = User::select('users.*')
  ->selectSub($lastLogin, 'last_login_at')
  ->get();

@foreach ($users as $user)
  <tr>
    <td>{{ $user->name }}</td>
    <td>{{ $user->email }}</td>
    <td>
      @if ($user->last_login_at)
        {{ $user->last_login_at->format('M j, Y \a\t g:i a') }}
      @else
        Never
      @endif
    </td>
  </tr>
@endforeach

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

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

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

Макрос

Прежде чем мы пойдем дальше, я хочу показать вам небольшой макрос, который я использую, чтобы свести использование подзапросов к минимуму. Мы сделаем это, добавив новый метод addSubSelect в QueryBuilder. Поместите следующий код в свой AppServiceProvider.

use Illuminate\Database\Query\Builder;

Builder::macro('addSubSelect', function ($column, $query) {
  if (is_null($this->columns)) {
    $this->select($this->from.'.*');
  }

  return $this->selectSub($query->limit(1), $column);
});

Не беспокойтесь о том, что делает этот макрос. Однако, если вам интересно:

  • Он добавляет select ('table. *') Для получения всех столбцов базовой таблицы в дополнение к новому столбцу подзапроса. Это необходимо, так как Laravel не будет включать «select *», если у вас есть какие-либо пользовательские selects, которые мы имеем в нашем подзапросе.
  • Он добавляет ограничение на один, поскольку подзапросы могут возвращать только одну строку.
  • Он вызывает метод selectSub.


Теперь давайте перепишем наш код с использованием нашего нового макроса:

$users = User::addSubSelect('last_login_at', Login::select('created_at')
  ->whereColumn('user_id', 'users.id')
  ->latest()
)->get();

Wow, выглядит просто супер!

Scopes

Еще одна вещь, прежде чем перейти к следующему шагу. Давайте переместим наш новый подзапрос в scope модели User:

class User extends Model
{
  public function scopeWithLastLoginDate($query)
  {
    $query->addSubSelect('last_login_at', Login::select('created_at')
      ->whereColumn('user_id', 'users.id')
      ->latest()
    );
  }
}

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

Я люблю скрывать лишний код в scope модели. Это не только упрощает контроллеры, но также позволяет легко переиспользовать эти запросы в другом месте. Кроме того, это поможет нам в следующем шаге, для загрузки динамических связей через подзапросы.

Динамические связи через подзапросы

Наконец-то мы добрались до ключевой части нашей статьи. Использование подзапроса для получения последней даты входа является отличным решением, но что, если мы хотим получить дополнительную информацию о последнем входе в систему? Например, возможно, мы хотим показать IP-адрес этого входа. Как нам это сделать?

Один из вариантов - просто создать второй scope для еще одного подзапроса:

$users = User::withLastLoginDate()->withLastLoginIpAddress()->get();

{{ $user->last_login_at->format('M j, Y \a\t g:i a') }} ({{ $user->last_login_ip_address }})

И, конечно же, это, безусловно, сработает. Но не лучше ли работать с фактическим экземпляром модели Login? Особенно, если эта модель имеет встроенные в нее дополнительные функции, такие как аксессоры или отношения.

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

{{ $user->lastLogin->created_at->format('M j, Y \a\t g:i a') }} ({{ $user->lastLogin->ip_address }})

Добро пожаловать, динамические отношения.

Мы начнем с определения нового belongs-to lastLogin связи. Теперь, как правило, чтобы связь belongs-to работала, вашей таблице нужен столбец для внешнего ключа. В нашем примере это означает, что в таблице наших пользователей будет столбец last_login_id. Однако, поскольку мы пытаемся избежать фактической денормализации и хранения этих данных в таблице users, мы будем использовать подзапрос, чтобы вместо этого выбрать внешний ключ. Eloquent не знает что это не настоящий столбец, а значит, все работает так, как будто это так. Давайте посмотрим на код:

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

  public function scopeWithLastLogin($query)
  {
    $query->addSubSelect('last_login_id', Login::select('id')
      ->whereColumn('user_id', 'users.id')
      ->latest()
    )->with('lastLogin');
  }
}

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

<table>
  <tr>
    <th>Name</th>
    <th>Email</th>
    <th>Last Login</th>
  </tr>
  @foreach ($users as $user)
    <tr>
      <td>{{ $user->name }}</td>
      <td>{{ $user->email }}</td>
      <td>
        @if ($user->lastLogin)
          {{ $user->lastLogin->created_at->format('M j, Y \a\t g:i a') }}
        @else
          Never
        @endif
      </td>
    </tr>
  @endforeach
</table>

Вот и все! Конечным результатом здесь является два запроса к базе данных. Давайте посмотрим на них:

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

Этот запрос в основном совпадает с запросом, который мы видели ранее, но вместо того, чтобы выбрать последнюю дату входа, мы выбираем последний идентификатор входа. Мы по существу получили столбец last_login_id, который мы бы добавили, если бы мы кэшировали значение, не имея при этом необходимости кэшировать его.

Теперь давайте посмотрим на второй запрос. Это запрос, который автоматически запускается Laravel, когда мы загружаем дату последнего входа, используя («lastLogin»):

select * from "logins" where "logins"."id" in (1, 3, 5, 13, 20 ... 676, 686)

Наш подзапрос позволил нам выбрать только последний логин для каждого пользователя. Кроме того, поскольку мы используем стандартные отношения Laravel для нашего lastLogin, мы также получаем эти записи как интанс модели Login. Действительно мило.

Ленивая загрузка для динамических связей

Одна вещь, о которой нужно знать при использовании данного подхода, заключается в том, что вы не можете лениво загружать динамические отношения из коробки. Это связано с тем, что наш scope не будет использован по умолчанию.

$lastLogin = User::first()->lastLogin; // вернет null

Если вы хотите, чтобы ленивая загрузка работала, вы все равно можете сделать это, добавив глобальный scope в свою модель:

class User extends Model
{
  protected static function boot()
  {
    parent::boot();

    static::addGlobalScope(function ($query) {
      $query->withLastLogin();
    });
  }
}

Лично я так не делаю. Я предпочитаю явно загружать мои динамические отношения, когда они нужны.

Можно ли достичь того же с помощью hasOne?

И на последок, прежде чем мы завершим. Возможно вы удивляетесь: "Почему бы просто не использовать связь hasOne, и не городить весь этого огород с подзапросами?". Краткий ответ: мы не можем достичь того же эффекта используя связь hasOne. Давайте посмотрим, почему.

Первый способ, который вы, возможно, думаете сработает, - сортировка запроса has-one:

public function lastLogin()
{
  return $this->hasOne(Login::class)->latest();
}

И, поначалу, это, по-видимому, дает желаемые результаты. Однако, если мы посмотрим на сгенерированный запрос, мы увидим проблему:

select * from "logins" where "logins"."user_id" in (1, 2, 3...99, 100) order by "created_at" desc

Он загружает все даты последнего входа пользователя user_id, без ограничений или фильтров. Это означает, что это не только загрузит последний вход, но и загрузит каждую запись для этого пользователя. Мы вернулись к проблеме загрузки 12 500 записей, которые мы видели ранее.

Но мы не сдаемся! Итак, добавим limit:

public function lastLogin()
{
  return $this->hasOne(Login::class)->latest()->limit(1);
}

Кажется, что этого вариант должен работать, но давайте посмотрим на сгенерированный запрос:

select * from "logins" where "logins"."user_id" in (1, 2, 3...99, 100) order by "created_at" desc limit 1

Laravel загружает отношения в одном запросе к базе данных, но теперь мы добавили limit 1. Это означает, что мы получим только одну запись для всех пользователей. Это будет запись входа для самого последнего пользователя, который должен войти в систему. Все остальные пользователи будут иметь null в этом поле.

Заключение

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

Перевод статьи Dynamic relationships in Laravel using subqueries

Опубликовано:

Категории: Статьи