I cannot seem to figure out a way to sort sql queries by joined children.
Database Example:
Table: posts
+------+---------+
| id | title |
+------+---------+
| 0 |'title1' |
| 1 |'title2' |
| 2 |'title3' |
+------+---------+
Table: post_meta
+------+---------+----------+---------+
| id | post_id | key | value |
+------+---------+----------+---------+
| 0 |0 |'coolness'| 5 |
| 1 |0 |'desc' |'random' |
| 2 |0 |'author' |'bill' |
| 3 |1 |'coolness'| 2 |
| 4 |1 |'desc ' |'random' |
| 5 |2 |'author' |'joe' |
| 6 |2 |'coolness'| 9 |
+------+---------+----------+---------+
I want a list of posts (or just post ids) ordered by their 'coolness' meta number (asc or desc). I dont know if I should be selecting from the posts table and joining on the meta table, or vise-versa. When I join on the post_meta table I only get data from one of the meta rows, so if I just add an order by post_meta.coolness nothing happens.
Thanks!
If you just want the id, you can use the post_meta table:
select pm.post_id
from post_meta pm
where pm.key = 'coolness'
order by pm.value + 0;
The + 0 is to convert the value (presumably a string) to a number.
If you need other columns related to the post, you can join in the posts table.