How can I order by the result of a recursive SQL query

I have the following method I need to ORDER BY:

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

I have gotten this far:

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

I may be looking at this wrong because I don't have experience with recursive SQL. Essentially I want to ORDER by whether a message or any of its parents has attachments. If it's attachment size is > 0, I can stop and return a 1. If the message has an attachment size of 0, I now check to see if it has a parent. If it has no parent then there is no attachment, however if it does have a parent then I essentially have to do the same query case logic for the parent.

UPDATE The table looks like this

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+------------开发者_JAVA百科----+
| 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       |                | 
+---------------------+--------------+------+-----+---------+----------------+

Where the parent_id refers to the parent message, if it exists. Thanks!

---

I've assumed that each attachment is stored in an attachments table with a message_id field.

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;