如何根据递归SQL查询的结果进行排序？

Question

我需要通过以下方法订购：

def has_attachments?
    attachments.size > 0  || (!parent.nil?  && parent.has_attachments?)
end

我已经走了这么远：

ORDER BY 
CASE WHEN attachments.size > 0 THEN 1 ELSE 
    (CASE WHEN parent_id IS NULL THEN 0 ELSE 
        (CASE message.parent ...what goes here ) 
                END
    END
END

我可能看错了，因为我没有使用递归SQL的经验。本质上，我想通过一个消息或它的任何父母是否有附件来订购。如果它的附件大小> 0，我可以停止并返回一个1。如果消息的附件大小为0，我现在检查它是否有父消息。如果它没有父类，那么就没有附件，但是如果它有一个父类，那么我基本上必须为父类执行相同的查询用例逻辑。

更新表如下所示

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment | 
| message_type_id     | int(11)      | NO   | MUL |         |                | 
| message_priority_id | int(11)      | NO   | MUL |         |                | 
| message_status_id   | int(11)      | NO   | MUL |         |                | 
| message_subject_id  | int(11)      | NO   | MUL |         |                | 
| from_user_id        | int(11)      | YES  | MUL | NULL    |                | 
| parent_id           | int(11)      | YES  | MUL | NULL    |                | 
| expires_at          | datetime     | YES  | MUL | NULL    |                | 
| subject_other       | varchar(255) | YES  |     | NULL    |                | 
| body                | text         | YES  |     | NULL    |                |  
| created_at          | datetime     | NO   | MUL |         |                | 
| updated_at          | datetime     | NO   |     |         |                | 
| lock_version        | int(11)      | NO   |     | 0       |                | 
+---------------------+--------------+------+-----+---------+----------------+

其中parent_id引用父消息(如果存在的话)。谢谢!

Answer 1

我假设每个附件都存储在一个带有message_id字段的附件表中。

WITH RECURSIVE msgs(id, parent_id, has_attachments, current_ancestor_id) AS
(
  SELECT DISTINCT
    m.id,
    m.parent_id,
    CASE WHEN a.message_id IS NULL THEN 0 ELSE 1 END AS has_attachments,
    -- If the message has attachments, there is no point in going to any ancestors
    CASE WHEN has_attachments = 0 THEN m.parent_id ELSE NULL END AS current_ancestor_id
  FROM messages m
  LEFT JOIN attachments a
      ON m.id = a.message_id

  UNION ALL

  SELECT
    m2.id,
    m2.parent_id,
    CASE WHEN (CASE WHEN a.message_id IS NULL THEN 0 ELSE 1 END) > m2.has_attachments THEN (CASE WHEN a.message_id IS NULL THEN 0 ELSE 1 END) ELSE m2.has_attachments END,
    CASE WHEN has_attachments = 0 THEN m1.parent_id ELSE NULL END AS current_ancestor_id
  FROM messages m1
  LEFT JOIN attachments a
      ON m1.id = a.message_id
  INNER JOIN msgs m2
      ON m1.id = m2.current_ancestor_id
)
SELECT
  id,
  parent_id,
  has_attachments
FROM msgs
WHERE current_ancestor_id IS NULL
ORDER BY
  has_attachments DESC;