Getting child-parent rows from same table; sort by different criteria based on row

Question

I am using MySQL. I already have a query that does one important part of this:

Get the newest parent row from the table comments, and for each parent comment, get the child's comments and sort by the oldest. Note: There is a column called parent_id. All parent's are 0, all children, of course, are greater than 0. Also, it is sorting on a column called create_date

This was very doable because the parent and children are both being sorted on create_date, (even if one was ASC and one was DESC).

Now I want to add the option for the users to sort by "top" comments, not just the "newest" as I am doing here:

SELECT * FROM (
SELECT c1.*, c1.create_date ac, c1.create_date ap FROM comments c1 
WHERE parent_id = 0 AND c1.profile_id = 582 
UNION 
SELECT c2.*, c2.create_date ac, p.create_date ap FROM comments c2 
JOIN comments p ON c2.parent_id = p.id 
WHERE c2.profile_id = 582 
) c
ORDER BY c.ap DESC, c.ac ASC;

So the caveat is now I want to sort the parents by a column called votes (DESC) and the children (or comment replies) the same as above: create_date ASC.

I have tried sub-querys, ORDER BY's with CASE statements and more. What do I need to do for this one sorting modification?

Note: Performance is nice, but not the hugest issue here. Also, I know I can do this with code + looping though an outer query -- but I rather not go that route if possible.

Answer 1

SELECT * FROM 
(
     SELECT c1.*, c1.vote_count totals, c1.parent_id pa_id, c1.id p_id, c1.create_date ac, c1.create_date ap FROM comments c1 
     WHERE parent_id = 0 AND c1.profile_id = 582 
     UNION 
     SELECT c2.*, p.vote_count totals, p.parent_id pa_id, p.id p_id, c2.create_date ac, p.create_date ap FROM comments c2 
     JOIN comments p ON c2.parent_id = p.id 
     WHERE c2.profile_id = 582 
) c
ORDER BY c.totals DESC, 
CASE
  WHEN c.pa_id = 0 THEN c.p_id
  ELSE c.parent_id
END ASC,
c.ac ASC

I just make a new alias column called totals that basically assigns the parent's vote count to the child so they will sort together.

Edit: I also had to add the parent comment's id and parent id for sorting purposes which is used in the new CASE statement in the ORDER BY