How can I order by the result of a recursive SQL query
- by Tony
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 |
+---------------------+--------------+------+-----+---------+----------------+
| 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!
