初めに

私現在、EC-CUBEのカスタマイズなどをしております。
今回は、案件の中で複合検索を作る機会があり少し複雑な処理を書いたのでメモ程度に残します。

状況

それぞれのカテゴリIDにて検索することはできるものの、

赤色、3000円以下で絞るなど複合検索ができないので、実装する。

知らない人のために、EC-CUBEのカテゴリの構造はこのようになっています。

カテゴリの構造

ID:1 色
    ID:4 赤
    ID:5 青

ID:2 用途
    ID:6 お出かけ

ID:3 予算
    ID:7 3000円以下
    ID:8 3000円~5000円
    ID:9 5000円以上

要件

・それぞれの大きなカテゴリ別ではorwhere検索(3000円以下と5000円以上選ぶとどちらかが当てはまればヒット)
・別のカテゴリではandwhere検索(赤色の3000円以下で検索した場合どちらも満たしているもののみヒット)

実装の内容

説明するよりコード見た方が早いと思うので載せます

class ProductRepository extends AbstractRepository
{
    public function getQueryBuilderBySearchData($searchData)
    
        $qb = $this->createQueryBuilder('p')
        ->andWhere('p.Status = 1')
        ->innerJoin('p.ProductCategories', 'pct')
        ->innerJoin('pct.Category', 'c');

        // クロス検索
        $q1 = clone $this;
        $q2 = clone $this;

        if (!empty($searchData['price']) && $searchData['price']){
            $prices = $searchData['price'];
            // サブクエリを作成
            $q = $q1->createQueryBuilder('u')
            ->innerJoin('u.ProductCategories', 'pct1')
            ->andwhere($q->expr()->in('pct1.Category', $prices))
            ->getDQL();

            $qb->andwhere($qb->expr()->in('p.id', sprintf('(%s)', $q)));
        }
        if (!empty($searchData['color']) && $searchData['color']){
            $colors = $searchData['color'];
            // サブクエリを作成
            $q = $q2->createQueryBuilder('i')
            ->innerJoin('i.ProductCategories', 'pct2')
            ->andwhere($q->expr()->in('pct2.Category', $colors))
            ->getDQL();

            $qb->andwhere($qb->expr()->in('p.id', sprintf('(%s)', $q)));
        }

        //////////////////// 省略 /////////////////////////////////
        return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
    }
}

ポイント1

->andwhere($q->expr()->in('pct1.Category', $prices))

これで同じカテゴリー同士のorwhere検索をしている。
返すのは、inner join しているテーブル(*下にサンプルあり)のカテゴリーIDが一致するもの。

inner join しているテーブル

id	name	product_id	category_id
1	サンプル1	1	2
2	サンプル1	1	4
3	サンプル2	2	2
4	サンプル2	2	5
5	サンプル2	2	6

categoryが2で絞り込まれた場合のテーブル

id	name	product_id	category_id
1	サンプル1	1	2
3	サンプル2	2	2

ポイント2

$qb->andwhere($qb->expr()->in('p.id', sprintf('(%s)', $q)));

categoryが2で絞り込まれたサブクエリから、idが一致する全ての行を取得する

idが一致する全ての行を取得したテーブル

id	name	product_id	category_id
1	サンプル1	1	2
2	サンプル1	1	4
3	サンプル2	2	2
4	サンプル2	2	5
5	サンプル2	2	6

こうすることによって次違うカテゴリIDで検索したときに、商品は絞り込まれているけど
絞り込んだカテゴリID以外も保持しているテーブルから検索できる。

逆に考えてみると既にcategoryが2で絞り込まれた場合のテーブルから
category4で絞り込もうとしたら、category4はそのテーブルに存在しないので、
必ず1件も返ってこないことになる。

ポイント3

$q1 = clone $this;

$this->createQueryBuilder()を使うと、その時の$thisからインスタンスが作られる。
サブクエリを作る時同じ $this を共有してしまうと、
ポイント2で話したように、必ず1件も返ってこないことになる。

そのため、一度クローンを作るサブクエリの数だけ量産しておき、それをサブクエリようのインスタンスとして使用する。

まとめ

サブクエリ使う前は、異なるジャンルで複合検索するときに絶対に1件も返ってこなくて、2時間くらい手が止まっていましたが、SQLを自分で書いてみてどんな結果が返ってくるのか丁寧に確認してやっと理解できました。
SQLの落とし穴に見事はまって大変でしたが、すごく勉強になりました！

インターネットの情報を頼りにやったので、一番スマートにできる方法ではないかもしれませんので、ご了承ください。
ご質問や、もっとやりやすい方法をご存知でしたら是非コメント残してください。