23
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【Laravel】 Eager Loading + LIMIT を UNION ALL で実現する

Last updated at Posted at 2018-07-19

問題

Eager Loading + LIMIT という組み合わせは,Laravel 上で Eloquent ビルダを使う以上そのままでは実現できない。

上記の例で booksを全件とってきて,それぞれの本に対する chapters の先頭5件 を取りたいとき

こうじゃなくて

SELECT * FROM `chapters` 
  WHERE `chapters`.`book_id` IN (1, 2, 3)
  ORDER BY `id`
  LIMIT 5

こうしてほしい!

SELECT * FROM `chapters` 
  WHERE `chapters`.`book_id` = 1
  ORDER BY `id`
  LIMIT 5
UNION ALL
SELECT * FROM `chapters` 
  WHERE `chapters`.`book_id` = 2
  ORDER BY `id`
  LIMIT 5
UNION ALL
SELECT * FROM `chapters` 
  WHERE `chapters`.`book_id` = 3
  ORDER BY `id`
  LIMIT 5

SELECT 回数は N+1 になってしまうが,クエリ発行回数自体は1回で済むため,通常よりもある程度は高速化される。N が十分小さければ大きな問題にはならないはずだ。

なお説明するまでもないと思うが,BelongsToMany など多対多リレーションでは正常に機能しないことに注意されたい。 HasMany MorphMany などに限られる。

解決策

マクロの定義

以下のサービスプロバイダを作成して config/app.php で登録。

app/Providers/MacroServiceProvider.php
<?php

declare(strict_types=1);

namespace App\Providers;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Str;

class MacroServiceProvider extends ServiceProvider
{
    /**
     * マクロを登録します。
     */
    public function boot(): void
    {
        Collection::macro('loadUsingLimit', function ($relations): Collection {
            /** @var Collection $collection */
            $collection = $this;
            return MacroServiceProvider::loadUsingLimit($collection, is_string($relations) ? func_get_args() : $relations);
        });
    }

    /**
     * EagerLoading を IN 句ではなく UNION ALL 句を使って行います。
     * LIMIT 句をエンティティごとに設けることができます。
     *
     * @param  Collection $collection
     * @param  array      $relations
     * @return Collection
     */
    public static function loadUsingLimit(Collection $collection, array $relations): Collection
    {
        foreach ($relations as $name => $constraints) {

            // 数値添字配列形式にも対応
            if (is_int($name)) {
                $name = $constraints;

                if (Str::contains($name, ':')) {
                    $name = explode(':', 'name', 2)[0];
                    $constraints = function (Relation $query) use ($name) {
                        $query->select(explode(',', explode(':', $name, 3)[1]));
                    };
                } else {
                    $constraints = function () {};
                }
            }

            if ($collection->isNotEmpty()) {
                /** @var Relation $relation */
                $relation = $collection
                    ->map(function (Model $model) use ($name) {
                        return $model->{$name}();
                    })
                    ->each(function (Relation $relation) use ($constraints) {
                        $constraints($relation);
                    })
                    ->reduce(function (?Relation $carry, Relation $query) {
                        return $carry ? $carry->unionAll($query) : $query;
                    });
                $relation->match(
                    $relation->initRelation($collection->all(), $name),
                    $relation->get(), $name
                );
            }
        }

        return $collection;
    }
}

使用例

名前空間などは省略

制約条件を取得時に定義
class Book extends Model
{
    public function chapters(): HasMany
    {
        return $this->hasMany(Chapter::class);
    }
}

class Chapter extends Model { }

$books = Book::all()->loadUsingLimit([
    'chapters' => function (HasMany $query) {
        $query->orderBy('id')->limit(5);
    },
]);
制約条件をリレーションとして定義
class Book extends Model
{
    public function chapters(): HasMany
    {
        return $this->hasMany(Chapter::class);
    }

    public function leadingChapters(int $count = 5): HasMany
    {
        return $this->chapters()->orderBy('id')->limit($count);
    }
}

class Chapter extends Model { }

$books = Book::all()->loadUsingLimit('leadingChapters');
23
15
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
23
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?