ORM order句でfieldを使ったソート

この記事は CakePHP Advent Calendar 2018 18日目の記事です。

やりたい事

ORMでDBからデータを取得する時、ある値だけを昇順や降順でソートし、取得したい。

order句でfieldを使用したソート

$test = $this->Hoge
    ->find()
    ->order([
        'field(id, 3, 5, 7)' => 'DESC'
    ]);

order句の連想配列は、keyがソート対象、valueがソート条件となっています。

fieldの第一引数には、ソート対象とするカラムを指定します。
今回の場合、idを対象としています。

第二引数以降は、第一引数に指定したカラムに存在する値を指定します。
今回はidカラムに存在する3,5,7を対象としています。

SELECT Hoge.id AS `Hoge__id`,
       Hoge.name AS `Hoge__name`
FROM hoge Hoge
ORDER BY field(id, 3, 5, 7) DESC

発行クエリはこのような形になります。

fieldの指定はORM独自の書き方ではなく、SQLのクエリの書き方です。
なので、order句で指定した書き方と全く同じ形でクエリが発行されるはずです。

contain句に指定したTableをfieldの条件にしたい場合

$test = $this->Hoge
    ->find()
    ->contain('Fuga')
    ->order([
        'field(Fuga.id, 2, 4, 6)' => 'DESC'
    ]);

次は、contain句でジョインしたテーブルをorder句の対象とした場合です。

contain句でジョインしたテーブルをorder句のfieldで指定する場合、カラム名の先頭にテーブル名を付与する必要があります。今回で言うところのFuga.idの部分です。

このような指定をする理由としては、参照先が不明確になるため、データの整合性が合わずエラーになるからです。
もし、'field(id, 2, 4, 6)'のように指定した場合、Database Errorになるはずです。

なので正確にいうと、ジョインしたテーブルをorder句で指定したい場合ではなく、ジョインして複数のテーブルからデータを取得するORMを書いた場合、order句での指定はカラム名の先頭にテーブル名を付けたテーブル名.カラム名としなくてはならない、が正しいです。

SELECT Hoge.id AS `Hoge__id`,
       Hoge.name AS `Hoge__name`,
       Fuga.id AS `Fuga__id`,
       Fuga.name AS `Fuga__name`
FROM hoge Hoge
LEFT JOIN fuga Fuga ON Fuga.id = (Hoge.fuga_id)
ORDER BY field(Fuga.id, 3, 5, 7) DESC

発行されたクエリがこちらです。
ジョインの結合条件on句の部分はTableクラスでアソシエーションが貼られていないと当たり前ですが、取得できません。
その場合おそらくエラー内容がThe ジョインしたテーブル名 association is not defined on ジョイン先テーブル名.みたいなエラーが表示されるかと思うので、order句によるエラーではない事はわかると思うのですが、念の為記載致します。

変数に格納された配列をfieldの条件にしたい場合

$array = [
    '1' => '3',
    '2' => '5',
    '3' => '7'
];

$test = $this->Hoge
    ->find()
    ->order([
        'field(id,' . implode(',', $array) . ')' => 'DESC'
    ]);

最後に変数に格納された配列の値を元にorder句でソートしたい場合です。

fieldでの指定方法は全く変わりません。第一引数に対象とするカラムを、第二引数に対象としたカラムの値を指定します。
このとき注意なのが、implodeで配列を文字列で連結してあげる必要があります。

implode後、上記の配列が3,5,7という形になるので、これで初めてソートできるようになります。

これをやらないとArray to string conversionとDatabase Errorの重ね技で怒られますので、要注意です。

SELECT Hoge.id AS `Hoge__id`,
       Hoge.name AS `Hoge__name`,
FROM hoge Hoge
ORDER BY field(id,3,5,7) DESC

こちらが発行クエリです。
implodeで整形した事によって、発行クエリの形は1番最初に紹介したソートと全く同じ状態になりました。

参考

https://book.cakephp.org/3.0/ja/orm/query-builder.html
http://php.net/manual/ja/function.implode.php
https://yoku0825.blogspot.com/2016/02/where-in-order-by-field.html
http://atsuizo.hatenadiary.jp/entry/2016/07/27/150000

おまけ

Twitterやってます！外部のエンジニアの方ともどんどん繋がりたいと考えていますので、是非フォローして頂ければと思います！@Tatsuo96
p.s.今年の2月までは溶接職人でした。ガッツ系エンジニアとしてやらせて頂いております。