存在しないカラムでorderを二重にかけたかったお話

はじめに

この記事は2023年度の振り返りです。

業務でちょっと考えた内容の備忘録です

問題

業務で、メッセージを既読・未読を取得する処理がありました。
既読は、既読順に配列に格納され、それを除外した残りが未読扱いとし、未読の中で作成順にソートがかけられて表示されていました。
(未読一覧の後に既読一覧が着ます)

一部を抜粋

read_message_ids = [5,3,2]
Message.order([Arel.sql('field(id, ?)'), read_message_ids])
       .order(created_at: :desc)

発行されたSQL

SELECT `messages`.* FROM `messages` ORDER BY field(id, 5,3,2), `messages`.`created_at` DESC

ただ、これの問題は、既読側も作成順で表示したいものの、配列順に表示されました。
配列をソートかけるのも手段の一つではあるものの、できればSQLで対応したいと思っていました。

解決策

一部抜粋

read_message_ids = [5,3,2]
read_message_add_flg = Message.where(id: read_message_ids).select('* , true as read_flg')
unread_message_add_flg = Message.where.not(id: read_message_ids).select('* , false as read_flg')
read_message_add_flg.union(unread_message_add_flg).order(:read_flg, message_created_at: :desc)

発行されたSQL

SELECT `messages`.* 
FROM ( 
    (SELECT * , true as read_flg FROM `messages` WHERE `messages`.`id` IN (5, 3, 2)) 
    UNION 
    (SELECT * , false as read_flg FROM `messages` WHERE `messages`.`id` NOT IN (5, 3, 2)) 
  ) `messages` 
ORDER BY `read_flg` ASC, `message_created_at` DESC

さいごに

一応union(gem)を利用して、望むSQLにはなったので良しとしました。
もし、他にもこういう方法があるよ！という方がいらっしゃれば、コメントに記載していただけると嬉しい限りです