条件を満たす組み合わせを抽出するSQL文

Question

前提

任意の条件を満たす商品の組み合わせを抽出したい

実現したいこと

itemsテーブル

id	name	price	weight
1	pen	100	11.5
2	note	150	8.3
3	paper_clip	40	1.3
4	glue	210	17.1
5	eraser	110	4.0
6	compasses	320	4.9

■流れ
①条件を選択して検索
└条件はprice「〜円まで」とweight「〜gまで」のいずれか、またはその両方
②①の条件を満たす組み合わせを抽出する
③②の結果からランダムで1つの組み合わせを返す

例1）price「250円まで」, weight「指定なし」
⇨[1, 2], [1, 3, 5], [2, 3], [3, 4], [3, 5]

例2）price「指定なし」, weight「〜12gまで」
⇨[1], [2, 3], [3, 5, 6], [5, 6]

例3）price「250円まで」, weight「〜12gまで」
⇨[1], [2, 3], [3, 5]

お力添え願いたいこと

②のSQL文

手段A.②で組み合わせをSQLで抽出してから、③をLaravelでランダムに抽出する
手段B.②③を同時にSQLで抽出する（技術的に可能？）

参考

サイゼリヤ1000円ガチャ( https://saizeriya-1000yen.marusho.io/ )

試したこと

ORDER BY RAND();でランダムに全件抽出し、抽出結果が条件を満たしているかを判別
満たす：成功
満たさない：もう一度SQLを回す（場合によってはなかなか終わらない）

⇨非常にナンセンスな書き方のため、一発で出せるSQL文はないか気になりご質問させていただきました。

Comments

[YT0014]

ORDER BY RAND()と再帰で作成可能な気がしますが、再帰は各DB毎に異なるので、対象DBをご提示いただいたほうが良いかと。

[Majespy]

回答が付きやすくなるようお気遣いいただき、ありがとうございます。

>再帰は各DB毎に異なるので、対象DBをご提示いただいたほうが良いかと。
→使用しているDBはMySQL8となります。

Answer 1

public function index(Request $request)
{
    $price = $request->price;
    $weight = $request->weight;

    $items = Item::query()
        ->when($price, function($q) use ($price) {
            $q->where('price', '=<', $price);
        })
        ->when($weight, function($q) use ($weight) {
            $q->where('weight', '=<', $weight);
        })
        ->inRandomOrder()
        ->get();

    $results = null;

    if ($items->isNotEmpty()) {
        $results = $items->pluck('id')->random(random_int(1, $items->count()));
    }

    dd($results);
}

Comments

[phper.k]

動作確認はしてないので、細かな修正はご自身で行ってください。

[Majespy]

ご回答ありがとうございます。

when句を除いて実行するとランダムで件数・データが取得できるのですが、when句をいれる（片方のみも試しました）と全てNULLで返ってきました。

この場合、when句の修正が必要という認識でお間違いないでしょうか。

[phper.k]

> when句を除いて実行するとランダムで件数・データが取得できるのですが、when句をいれる（片方のみも試しました）と全てNULLで返ってきました。

何言っているのかわからん

Answer 2

②①の条件を満たす組み合わせを抽出する

再帰SQLを利用すると、実現可能です。再帰SQLの構文はDBMSごとに少しずつ異なるので、ここではMySQL8で例3を実装したものを示します。例1や例2はWHERE節の条件式を変更すれば実装可能です。

WITH RECURSIVE cte AS (
    SELECT id, price, weight, CAST(id AS CHAR) AS combination
    FROM items
    WHERE price <= 250 AND weight <= 12 
  UNION ALL
    SELECT I.id, C.price + I.price, C.weight + I.weight, CONCAT(C.combination, ',', I.id) 
    FROM cte AS C
    JOIN items AS I ON C.id < I.id
    WHERE C.price + I.price <= 250 AND C.weight + I.weight <= 12
)
SELECT combination FROM cte

Comments

[Majespy]

ご回答ありがとうございます。

再帰SQLなるものがあるのですね、ただただ勉強不足でした。
大変恐縮ですが、これをEloquentで表現する場合どういった構文になるのでしょうか。

基本となるSQL文（WITH　RECURSIVE） と比較しながら理解したいため、
お手数をおかけしますが対象となる構文ちょうだいできませんでしょうか。

[neko_the_shadow]

PHPに詳しくないので、ちょっとわからないです。申し訳ありません。たとえば以下のような記事は参考になるでしょうか?

https://crieit.net/posts/mysql80-with-recursive-in-standalone-eloquent-20220712