EC-CUBE4のSQLで特定のIDの商品を先頭に表示したい場合の書き方
はじめに
EC-CUBEのカスタマイズ案件の中で、「商品一覧の先頭に指定した商品を指定した順に表示させる」という要望がありました。
MySQLだと慣れているので問題ないのですが、
EC-CUBE4ではSQLではなくDoctrineのDQLのため、作法が違う部分がいくつかあります。
そこで、EC-CUBEでうまくいった方法をまとめます。
対象環境
- MySQL8
- EC-CUBE4
やりたいこと
仕様
- IDを指定した場合、その商品を先頭に持ってくる
- IDは複数指定可能
- IDを指定していない商品は、指定した商品の後にID順に並べる
次のようなデータがあった場合
id | name |
---|---|
1 | いちご |
2 | りんご |
3 | バナナ |
4 | メロン |
5 | すいか |
ID [4, 2] を指定して、こう表示させたい
id | name |
---|---|
4 | メロン |
2 | りんご |
1 | いちご |
3 | バナナ |
5 | すいか |
MySQLでのやり方
① order by句で直接指定
SELECT * FROM table ORDER BY id = '4' DESC, id = '2' DESC, id;
② FIELD関数を利用する
FIELD関数は知らなかったのですが、このやり方でもできるようです。
(本仕様を満たすにはIDを逆順に指定する必要があるので気持ち悪いですが)
SELECT * FROM table ORDER BY FIELD(id, '2', '4') DESC, id;
EC-CUBE(Doctrine)での問題
EC-CUBEのクエリビルダで上記のSQLを書こうとしたら、うまくいきませんでした。
① order by句で直接指定
orderBy関数にはカラム名しか指定できないためエラーとなる
例:
... ->orderBy('id = 4', 'DESC') // <- error! ->addOrderBy('id = 2', 'DESC') // <- error! ->addOrderBy('id')
(※変数のエスケープ処理はひとまず無視)
② FIELD関数を利用する方法
DoctrineにMySQL関数が実装されていないため使えません。
※DoctrineExtensionsというものを入れると使えるようです。
参考
- GitHub - beberlei/DoctrineExtensions: A set of Doctrine 2 extensions
- SymfonyでDQLを拡張してMysqlの関数を使えるようにする - Qiita
ですが、今回はデフォルトの機能で再現する方法を紹介します。
解決方法
CASE文を使って、条件に合致する要素を先頭に持ってくるように設定しました。
sort専用のパラメータを新しく作って、それを基準に並べ替えています。
$qb = $this->createQueryBuilder('p') ->addSelect('CASE WHEN p.id = 4 THEN 1 ELSE 0 END as HIDDEN sort_id1') ->addSelect('CASE WHEN p.id = 2 THEN 1 ELSE 0 END as HIDDEN sort_id2') ->orderBy('sort_id1', 'DESC') ->addOrderBy('sort_id2', 'DESC') ->addOrderBy('p.id'); $result = $qb->getQuery()->getResult();
結果
id | name |
---|---|
4 | メロン |
2 | りんご |
1 | いちご |
3 | バナナ |
5 | すいか |
また、HIDDENキーワードを使った要素は取得結果に含まれないので、ソートの為に作った要素が取得オブジェクトには入らずすっきりします。
終わりに
素のSQLなら簡単にできることでもORMをかますと工夫が必要なことがあります。 複雑になってくるとSQLを直接書いたほうが早いこともありますが、可能ならORMの機能を生かすほうが良いですね。
EC-CUBE4(というかDoctrine)にまだ慣れていないので他に良い方法があるかもしれません。