MySQLの昇順ソートの問題
それは、昇順でソートすると、基準カラムがNULLの場合、それが先頭に来ることだ。実装としては正しいかもしれないが、NULLが最後に来てほしい場合もある。
しかし、MySQLには(少なくとも4.1系には)それを指定するオプションがない。よって、昇順にソートすると次のようになる。
- 例:面接に予約した学生を予約日順に昇順ソートする
SELECT students.id,held_date_time FROM students LEFT JOIN interviews on students.interview_id = interviews.id ORDER BY held_date_time; +----+---------------------+ | id | held_date_time | +----+---------------------+ | 2 | NULL | | 4 | NULL | | 6 | NULL | | 9 | NULL | | 12 | NULL | | 13 | NULL | | 56 | NULL | | 57 | NULL | | 58 | NULL | | 8 | 2007-09-01 09:00:00 | | 5 | 2007-09-10 14:00:00 | | 14 | 2007-09-10 14:00:00 | | 15 | 2007-09-10 14:00:00 | | 10 | 2007-10-18 20:00:00 | | 55 | 2007-11-07 15:00:00 | | 7 | 2007-12-08 13:00:00 | +----+---------------------+
本当は、2007-12-08 13:00:00の後に、NULLの人がずらずら並んでほしい。
昇順ソートでNULLを後ろにもってくる
解決策の1つとして、NULLの場合は○○とみなすという風にして、ソートをだますことにする。つまり、NULLだったら、それはずっと未来のこと(昇順で後ろにくる日)とみなすということだ。
具体的には次のようになる。
SELECT students.id,held_date_time, FROM students LEFT JOIN interviews ON students.interview_id = interviews.id ORDER BY CASE WHEN held_date_time IS NULL THEN DATE_ADD(NOW(),INTERVAL 1 YEAR) ELSE held_date_time END ; +----+---------------------+---------------------+ | id | held_date_time | for_order | +----+---------------------+---------------------+ | 8 | 2007-09-01 09:00:00 | 2007-09-01 09:00:00 | | 5 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 | | 14 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 | | 15 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 | | 10 | 2007-10-18 20:00:00 | 2007-10-18 20:00:00 | | 55 | 2007-11-07 15:00:00 | 2007-11-07 15:00:00 | | 7 | 2007-12-08 13:00:00 | 2007-12-08 13:00:00 | | 58 | NULL | 2009-01-04 09:32:01 | | 9 | NULL | 2009-01-04 09:32:01 | | 12 | NULL | 2009-01-04 09:32:01 | | 13 | NULL | 2009-01-04 09:32:01 | | 2 | NULL | 2009-01-04 09:32:01 | | 56 | NULL | 2009-01-04 09:32:01 | | 4 | NULL | 2009-01-04 09:32:01 | | 57 | NULL | 2009-01-04 09:32:01 | | 6 | NULL | 2009-01-04 09:32:01 | +----+---------------------+---------------------+
場当たり的な気もしますが、このような機能が付いているRDBMSのコードを読んでみるのもいいかもなー
- 参考元
追記:Railsで使う際の注意(find_by_sqlの注意かな)
上記のSQLをRailsで使う場合(何もRailsに限らないと思うが)、注意する点がある。例えば、上記を実行する際に、以下のようなコードを書いたとする。
- バグありのコード
@students = Student.find_by_sql(<<-SQL) SELECT *, FROM students LEFT JOIN interviews ON students.interview_id = interviews.id ORDER BY CASE WHEN held_date_time IS NULL THEN DATE_ADD(NOW(),INTERVAL 1 YEAR) ELSE held_date_time END SQL
だが、この状態で次のコードを実行してみる。
@students.first.id #=> 112 Student.find(112) # => ActiveRecord::RecordNotFound: Couldn't find Student with ID=112 @students.first.interview.id #=> 112
何故か、Studentオブジェクトから引いたidを使って検索をかけても、エラーが生じてしまう。実は、上記の1行目の結果のidは、元のSQLで言えば、students.idでなく、interviews.idなのである。(3行目の結果からそれが分かる)
このようなエラーが起こると、@studentsをビューで表示した際に、以下のようなコードでエラーが発生する。
link_to "詳細",:action => show,:id => @students.first #=> idをもつStudentオブジェクトが存在しないためエラー
何故上記のようなことが起こるのか
これは、簡単で、「バグありのコード」の結果の@studentsは、LEFT JOINした結果であって、純粋なStudentオブジェクトではないから。つまり、JOINで連結されるカラムのheld_date_timeやdummyも持ってしまっている。
これは、「バグありのコードの直後に以下を実行すると分かる」
@students.first.dummy #=> "2007-09-01 09:00:00" @students.first.held_date_time #=> "2007-09-01 09:00:00"
よって、理論上@students.firstの中には、students.idとinterviews.idが含まれることになる。しかし、結果のStudentオブジェクトにはどちらもidという名で入るため、どちらかが上書きされるはめになる。(ハッシュは同じキーをもてないため)
よって、@students.first.idは、students.idでなく、interviews.idを参照し、@students.fisrtから直接Studentオブジェクトのidを得ることはできない。
解決策
上記の問題は、JOINの連結結果を取得してるから起こるのであって、結果から、studentsのカラムだけ集めてくれば起こらない。よって、「バグありのコード」を修正すると次のようになる。
- 「バグありのコード」修正版(SELECTの対象が変化)
@students = Student.find_by_sql(<<-SQL) SELECT students.* FROM students LEFT JOIN interviews ON students.interview_id = interviews.id ORDER BY CASE WHEN held_date_time IS NULL THEN DATE_ADD(NOW(),INTERVAL 1 YEAR) ELSE held_date_time END SQL
追記:MySQLの場合「COALESCE」を使うとスマート
- COALESCE(レファレンスより)
リスト内の最初の非 NULL 要素を返す。
SELECT COALESCE(NULL,1); -> 1 SELECT COALESCE(NULL,NULL,NULL); -> NULL
これを使うと、上記のコードが以下のようになる。find_by_sql使わなくてもいいね。さすれば、「Railsで使う際の注意(find_by_sqlの注意かな)」も気にする必要がなくなるし。
Student.find( :all, :include=>:interview, :order=>”COALESCE(held_date_time, DATE_ADD(NOW(),INTERVAL 1 YEAR))” )
「舞波」さんコメントありがとうございます☆
追々記:もっとスマートにできる
id:MeijiKさんのコメントより。
SELECT students.id,held_date_time, FROM students LEFT JOIN interviews ON students.interview_id = interviews.id ORDER BY held_date_time IS NULL, held_date_time ;
MySQLのリファレンスを読んだところ、「IS NULL関数」は、真偽を「0/1」で返すので、それをソート基準に使えるということです。
よって、最初のソートで、held_date_timeがNULLでないレコードが上から並び、それ以降にNULLのレコードが並びます。
次に、held_date_timeでソートをかければ、held_date_timeがNULLのグループ内で(これはソートされないけど)、held_date_timeがNULLでないグループ内でheld_date_time基準のソートがかかり、held_date_timeがNULLでないレコードがきちんとソートされるという寸法です。
この発想はなかったです。MeijiKさん++です(*´艸`)