EC-CUBE4のSQLで特定のIDの商品を先頭に表示したい場合の書き方

はじめに

EC-CUBEのカスタマイズ案件の中で、「商品一覧の先頭に指定した商品を指定した順に表示させる」という要望がありました。

MySQLだと慣れているので問題ないのですが、
EC-CUBE4ではSQLではなくDoctrineのDQLのため、作法が違う部分がいくつかあります。

そこで、EC-CUBEでうまくいった方法をまとめます。

対象環境

• MySQL8
• EC-CUBE4

やりたいこと

仕様

• IDを指定した場合、その商品を先頭に持ってくる
  • IDは複数指定可能
• IDを指定していない商品は、指定した商品の後にID順に並べる

次のようなデータがあった場合

ID [4, 2] を指定して、こう表示させたい

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というものを入れると使えるようです。

参考

• https://github.com/beberlei/DoctrineExtensions
• https://qiita.com/haruna-nagayoshi/items/5a9f08e3b337bf36c227

ですが、今回はデフォルトの機能で再現する方法を紹介します。

解決方法

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();

結果

また、HIDDENキーワードを使った要素は取得結果に含まれないので、ソートの為に作った要素が取得オブジェクトには入らずすっきりします。

終わりに

素のSQLなら簡単にできることでもORMをかますと工夫が必要なことがあります。
複雑になってくるとSQLを直接書いたほうが早いこともありますが、可能ならORMの機能を生かすほうが良いですね。

EC-CUBE4（というかDoctrine）にまだ慣れていないので他に良い方法があるかもしれません。