Go to Qiita Advent Calendar 2024 Top
LoginSignup
22
14

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.

@mpyw(mpyw)

【Laravel】 Eager Loading でネストしたリレーションをカウントする

Last updated at Posted at 2018-09-15

概要

  • posts morphMany comments
    （いろいろなものに付けられるコメントはその1つとして投稿に属する）
  • comments hasMany replies
    （返信はコメントに属する）

という2階層のリレーションがあったとき， posts から comments_and_replies_count に相当するものを取得したい。できるだけ Eager Loading を使ってパフォーマンスに配慮して。

普通に

protected $withCount = ['comments', 'comments.replies'];

のようにモデルに書くだけではこの要件には対応できないので，独自のリレーションを定義することにする。

実装方針

  • 独自リレーション HasCommentsAndRepliesCount を定義
  • モデルから簡単に使えるように HasExtendedRelationships トレイトを作成
  • スカラー値リレーションへの対応のために HasScalarRelations トレイトを作成

実装例

リレーション側

app/Database/Eloquent/Relations/HasCommentsAndRepliesCount.php
<?php

declare(strict_types=1);

namespace App\Database\Eloquent\Relations;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphOne;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\DB;

/**
 * Class HasCommentsAndRepliesCount
 *
 * コメント数とそれに属す返信数を同時に集計します。
 * Eager Loading に対応しています。
 */
class HasCommentsAndRepliesCount extends MorphOne
{
    /**
     * Set the base constraints on the relation query.
     */
    public function addConstraints(): void
    {
        if (static::$constraints) {
            // $this->query に commentable_type と commentable_id と deleted_at の制約を付与
            parent::addConstraints();

            // コメント数の集計
            $x = (clone $this->query)
                ->selectRaw('count(*)');

            // コメントに属する返信数の集計
            $y = (clone $this->query)
                ->selectRaw('count(*)')
                ->join('replies', function (JoinClause $join) {
                    $join->on('replies.comment_id', '=', 'comments.id');
                    $join->whereNull('replies.deleted_at');  // JOINはグローバルスコープを自分で付与
                });

            // $x + $y の値を取得
            $this->query = $this->newModelInstance()->newModelQuery()
                ->from(null) // FROM句を消去
                ->selectRaw("({$x->toSql()}) + ({$y->toSql()}) as `comments_and_replies_count`")
                ->setBindings(array_merge([$x->getBindings(), $y->getBindings()]));
        }
    }

    /**
     * Set the constraints for an eager load of the relation.
     *
     * @param Model[] $models
     */
    public function addEagerConstraints(array $models): void
    {
        // $this->query に commentable_type と commentable_id と deleted_at の制約を付与
        parent::addEagerConstraints($models);

        // コメント数の集計
        $x = (clone $this->query)
            ->selectRaw('count(*), `comments`.`commentable_type`, `comments`.`commentable_id`')
            ->groupBy('comments.commentable_type', 'comments.commentable_id');

        // コメントに属する返信数の集計
        $y = (clone $this->query)
            ->selectRaw('count(*), `comments`.`commentable_type`, `comments`.`commentable_id`')
            ->join('replies', function (JoinClause $join) {
                $join->on('replies.comment_id', '=', 'comments.id');
                $join->whereNull('replies.deleted_at'); // JOINはグローバルスコープを自分で付与
            })
            ->groupBy('comments.commentable_type', 'comments.commentable_id');

        // UNION ALL で結合
        $z = $x->unionAll($y);

        // SUM(`count(*)`) の値を取得
        $this->query = $this->newModelInstance()->newModelQuery()
            ->selectRaw('sum(`count(*)`) as `comments_and_replies_count`, `descendants`.`commentable_type`, `descendants`.`commentable_id`')
            ->from(DB::raw("({$z->toSql()}) as `descendants`"))
            ->setBindings($z->getBindings())
            ->groupBy('descendants.commentable_type', 'descendants.commentable_id');
    }

    /**
     * Initialize the relation on a set of models.
     *
     * @param  array  $models
     * @param  string $relation
     * @return array
     */
    public function initRelation(array $models, $relation): array
    {
        foreach ($models as $model) {
            $model->setRelation($relation, 0); // Eager Loading 前に埋める初期値は 0
        }
        return $models;
    }

    /**
     * Get the value of a relationship by one or many type.
     *
     * @param  array  $dictionary
     * @param  string $key
     * @param  string $type
     * @return int
     */
    protected function getRelationValue(array $dictionary, $key, $type): int
    {
        $value = $dictionary[$key];
        return (int)(reset($value)->comments_and_replies_count); // Eager Loading 後に1行目の comments_and_replies_count フィールドを取り出す
    }

    /**
     * Get the results of the relationship.
     *
     * @return int
     */
    public function getResults(): int
    {
        return (int)($this->query->first()->comments_and_replies_count ?? '0'); // Lazy Loading 後に comments_and_replies_count フィールドがあれば取り出し，無ければ 0 とする
    }
}
app/Database/Eloquent/Concerns/HasExtendedRelationships.php
<?php

declare(strict_types=1);

namespace App\Database\Eloquent\Concerns;

use App\Comment;
use App\Database\Eloquent\Relations\HasCommentsAndRepliesCount;
use Illuminate\Database\Eloquent\Model;

/**
 * Trait HasExtendedRelationships
 *
 * 拡張リレーションを定義します。
 *
 * @mixin Model
 */
trait HasExtendedRelationships
{
    /**
     * コメント数とそれに属す返信数を同時に集計します。
     * Eager Loading に対応しています。
     *
     * @param  string                     $localKey
     * @return HasCommentsAndRepliesCount
     */
    public function hasCommentsAndRepliesCount(string $localKey = null): HasCommentsAndRepliesCount
    {
        /** @var Comment $instance */
        $instance = $this->newRelatedInstance(Comment::class);
        $table = $instance->getTable();

        return new HasCommentsAndRepliesCount(
            $instance->newQueryWithoutRelationships(),
            $this,
            "$table.commentable_type",
            "$table.commentable_id",
            $localKey ?: $this->getKeyName()
        );
    }
}
app/Database/Eloquent/Concerns/HasScalarRelations.php
<?php

declare(strict_types=1);

namespace App\Database\Eloquent\Concerns;

use Illuminate\Contracts\Support\Arrayable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;

/**
 * Trait HasScalarRelations
 *
 * Arrayable または null ではない整数値や文字列などのリレーションを取得できるようにします。
 * もとの実装では「Arrayable または null 以外は無効な値」とされていますが，
 * この実装でも Eager Loading とプロパティアクセス形式のリレーション読み込みでは問題なく動きます。
 *
 * @mixin Model
 */
trait HasScalarRelations
{
    /**
     * Get the model's relationships in array form.
     *
     * @return array
     */
    public function relationsToArray(): array
    {
        $attributes = [];

        foreach ($this->getArrayableRelations() as $key => $value) {
            $attributes[
                static::$snakeAttributes
                    ? Str::snake($key)
                    : $key
            ] = $value instanceof Arrayable
                ? $value->toArray()
                : $value;
        }

        return $attributes;
    }
}

モデル側

app/Post.php
<?php

declare(strict_types=1);

namespace App;

use App\Database\Eloquent\Concerns\HasExtendedRelationships;
use App\Database\Eloquent\Concerns\HasScalarRelations;
use App\Database\Eloquent\Relations\HasCommentsAndRepliesCount;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\MorphMany;
use Illuminate\Database\Eloquent\SoftDeletes;

class Post extends Model
{
    use SoftDeletes, HasExtendedRelationships, HasScalarRelations;

    protected $casts = [
        'id' => 'int',
        'type' => 'string',
        'title' => 'string',
        'body' => 'string',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    protected $visible = [
        'id',
        'title',
        'body',
        'created_at',
        'updated_at',
        'comments',
        'commentsAndRepliesCount', // 可視化する
    ];

    protected $fillable = ['title', 'body'];
    protected $with = ['commentsAndRepliesCount']; // 自動的に読み込む

    /**
     * コメント+返信数を取得します。
     *
     * @return HasCommentsAndRepliesCount
     */
    public function commentsAndRepliesCount(): HasCommentsAndRepliesCount
    {
        return $this->hasCommentsAndRepliesCount();
    }

    /**
     * 投稿に属するコメントを取得します。
     *
     * @return MorphMany
     */
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}
app/Comment.php
省略
app/Reply.php
省略

生成されるSQLと実行計画

Lazy Loading

select (

  select count(*) from `comments` 
  where `comments`.`commentable_id` = 1
    and `comments`.`commentable_id` is not null 
    and `comments`.`commentable_type` = 'post' 
    and `comments`.`deleted_at` is null

) + (

  select count(*) from   `comments` 
  inner join `replies`
          on `replies`.`comment_id` = `comments`.`id`
         and `replies`.`deleted_at` is null 
  where `comments`.`commentable_id` = 1 
    and `comments`.`commentable_id` is not null 
    and `comments`.`commentable_type` = 'post' 
    and `comments`.`deleted_at` is null

) as `comments_and_replies_count`

Lazy Loading の実行計画

Eager Loading

select sum(`count(*)`) as `comments_and_replies_count`, 
       `descendants`.`commentable_type`, 
       `descendants`.`commentable_id` 
from ((

   select count(*), 
          `comments`.`commentable_type`, 
          `comments`.`commentable_id` 
     from `comments` 
     where `comments`.`commentable_id` in ( 1, 2, 3, 4, 5 ) 
       and `comments`.`commentable_type` = 'post'
       and `comments`.`deleted_at` is null 
  group by `comments`.`commentable_type`, `comments`.`commentable_id`

) union all (

   select count(*), 
          `comments`.`commentable_type`, 
          `comments`.`commentable_id` 
     from `comments` 
     inner join `replies` 
             on `replies`.`comment_id` = `comments`.`id` 
            and `replies`.`deleted_at` is null 
     where `comments`.`commentable_id` in ( 1, 2, 3, 4, 5 ) 
       and `comments`.`commentable_type` = 'post'
       and `comments`.`deleted_at` is null 
  group by `comments`.`commentable_type`, `comments`.`commentable_id`

)) as `descendants` 
group by `descendants`.`commentable_type`, `descendants`.`commentable_id`

Eager Loading の実行計画

22
14
2

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
Sign upLogin
22
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?