「複数あるリレーション先のデータの最小値」からリレーション元をソートしたい

Question

前提・実現したいこと

リレーション先の値を使って親データをソートしようと考えています。

商品名を入力し、下図のような検索結果がでた場合に、98円のセブンイレブンが一番上にくるようにしたいイメージです。

調べたところ、こちらのサイトの方法がヒットしたのですが、自分の環境での適応がうまくできなかったのでアドバイス頂きたいです。

データベースは下図のようにしており、
has many 中間テーブル
├ belongs to 商品 └belongs to 店名
└ has many 金額や数量
のような形をとっています。

該当のソースコード

現在のコントローラ

    $items = Item::with('middle.shop', 'middle.information')
        -> where('name', 'LIKE', "%$query%")
        -> get();

試したこと

joinでテーブルをつなぎ金額でソートしてからgroupBy()するようなことも試みたのですが、カラムが全て同じ階層になってしまうのでビューのテーブル作成がうまくできませんでした…

補足情報（FW/ツールのバージョンなど）

PHP Version 7.1.26
Laravel Framework 5.8.4

Answer 1

Not exists 最適化狙いの戦略はどうでしょうか？

class ItemShop extends Pivot
{
    protected $table = 'item_shop';

    public function scopeOrderByLowestMoney(Builder $query): Builder
    {
        return $query
            ->join('information', 'item_shop.id', '=', 'information.item_shop_id')
            ->leftJoin('information as filter', function (JoinClause $join) {
                $join->on('information.item_shop_id', '=', 'filter.item_shop_id');
                $join->on(function (JoinClause $join) {
                    $join->on('information.id', '>', 'filter.id');
                    $join->on('information.money', '=', 'filter.money');
                    $join->orOn('information.money', '>', 'filter.money');
                });
            })
            ->whereNull('filter.id')
            ->select('item_shop.*')
            ->orderBy('information.money')
            ->orderBy('information.id');
    }

    public function scopeWhereItem(Builder $query, ...$args): Builder
    {
        return $query
            ->join('items', 'item_shop.item_id', '=', 'items.id')
            ->where(function (Builder $query) use ($args) {
                $query->setModel(new Item())->where(...$args);
            });
    }

    public function item(): BelongsTo
    {
        return $this->belongsTo(Item::class);
    }

    public function shop(): BelongsTo
    {
        return $this->belongsTo(Shop::class);
    }

    public function information(): HasMany
    {
        return $this->hasMany(Information::class);
    }
}

まず発想の転換が必要です。結果的に 表示したいレコードは item_shop テーブル行単位 なので，カスタムピボットクラス ItemShop が必要なことに注意しましょう。これに Item と Shop と Information を後から Eager Loading で取ってくるという流れでいきましょう。
（複数商品が選択された場合，同じ店舗が商品数ぶんだけ現れてもいいと考える）

item_shop テーブルのソートのために，information を内部結合します。さらに繰り返して外部結合を行い，この際は 「金額がより高いもの」 または 「金額が同じだが情報IDがより大きいもの」 だけを対象として，結合相手が存在しなかったところには NULL を埋めます。そして，NULL が埋められたところだけを逆に残せば 「金額が最も安いもの」 だけが残ります。これをスコープとして定義しましょう。

items に関しては whereHas を使っても解決できますが，クロージャだと書きにくい上サブクエリだとパフォーマンスが劣化する可能性に繋がるので，こちらもついでにスコープとして定義しておきます。

ItemShop::with('item', 'shop', 'information')
    ->whereItem('items.name', 'like', '%' . addcslashes($query, '\_%') . '%')
    ->orderByLowestMoney()
    ->get();

LIKE 検索における \ _ % のエスケープも忘れずに。また，最安値が重複したときのために第二ソート軸として一意な id も入れておきましょう。

---

最初に実行されるSQL（参考）

select
    `item_shop`.*
from
    `item_shop`
    inner join
        `items`
    on  `item_shop`.`item_id` = `items`.`id`
    inner join
        `information`
    on  `item_shop`.`id` = `information`.`item_shop_id`
    left join
        `information` as `filter`
    on  `information`.`item_shop_id` = `filter`.`item_shop_id`
    and (
            `information`.`id` > `filter`.`id`
        and `information`.`money` = `filter`.`money`
        or  `information`.`money` > `filter`.`money`
        )
where
    (`items`.`name` like ?)
and `filter`.`id` is null
order by
    `information`.`money` asc,
    `information`.`id` asc

Comments

[mpyw]

まあ Not Exists 最適化があるといっても（プレフィクスマッチングではない）LIKE検索を使っている時点でパフォーマンス面はお察しなので，あまり期待はせず…

[riz]

丁寧なご回答ありがとうございます！
知らなかったことばかりで、非常に参考になりました。
また、エスケープやidによるソートまでご親切にありがとうございます。

頂いた方法を実際に組み込んでみたところ、orderByMoney()で見事に価格順に並べられたのですが、with()を通す際に検索結果数がヒット数×ヒット数となってしまい、上のバナナの例の順番で同じ結果が5つ並ぶような形になってしまいました。

スコープ関数からクエリービルダー、サブクエリまでほとんど理解できていないので、ひとまずキャッチアップしなが解決策を探してみます。

[mpyw]

1つの item_shop に対して複数の information があるということでしょうか…？

[mpyw]

（↑いや，それでも動くはず…なんでだろう）

[mpyw]

ちょっと勘違いしてました，修正します。item_id ではなく item_shop_id で一意な結果を残すべきでした

[mpyw]

大幅に加筆修正しました！

[riz]

ありがとうございます！
おかげさまで、思い描いていた通りの実装ができました。
まだ「何が何だか分かっていないコードがとりあえず動かせている」という状態ですので、数日かけて頂いた情報を参考に動きを勉強させてもらおうと思います。

ここまで丁寧に回答を頂けたのが凄くありがたいので、お気持ち程度ですが、公開メールアドレスにアマギフ投げ銭させて頂きます！

[mpyw]

恐縮です！こちらこそありがとうございました。

[mpyw]

（蛇足ですが，MySQL 8.0 以降または PostgreSQL の場合は，ウィンドウ関数を使うと「LEFT JOIN ... WHERE id IS NULL」の Not Exists 最適化狙いのクエリよりも高い可読性と高いパフォーマンスを両立できます。今回はあくまで MySQL 5.x 向けのクエリです）