Mysql一对多的关系。 按第二个表中的行数排序

Question

I have created a voting system in php and mysql. When a user votes on an id, a record is inserted in "votes" referencing the FK media_id. When I then display the entries I use this query to get the number of votes for each entry:

$sql = "SELECT COUNT(*) FROM insta_votes WHERE media_id ='".$mediaid."'";
if ($res = $db->query($sql)) {
    return $res->fetchColumn();
}
return 0;

This works fine, but I want to be able to sort the results by the number of votes they have. Preferably using just one query. How can I achieve this?

The tables are structured like this:

votes table
+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment |
| media_id  | varchar(255) | NO   |     | NULL              |                |
| ip        | varchar(20)  | NO   |     | NULL              |                |
| c_time    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| sessionid | varchar(30)  | NO   |     | NULL              |                |
+-----------+--------------+------+-----+-------------------+----------------+

entries table
+---------------+--------------+------+-----+-------------------+-------+
| Field         | Type         | Null | Key | Default           | Extra |
+---------------+--------------+------+-----+-------------------+-------+
| page_id       | int(11)      | NO   | MUL | NULL              |       |
| media_id      | varchar(255) | NO   | PRI | NULL              |       |
| url           | varchar(255) | NO   |     | NULL              |       |
| c_time        | datetime     | NO   |     | NULL              |       |
| likes         | int(11)      | YES  |     | NULL              |       |
| deleted       | tinyint(1)   | NO   |     | 0                 |       |
| inserted_time | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
| numReports    | int(11)      | NO   |     | 0                 |       |
+---------------+--------------+------+-----+-------------------+-------+

Thank you!

Answer 1

If I understand the tables correctly (and I may not), each entries row may reference multiple votes rows. In that case, the query you need will go something like this:

SELECT
  entries.page_id,
  COUNT(*) AS VoteCount
FROM entries
INNER JOIN votes ON entries.media_id = votes.media_id
GROUP BY entries.page_id
ORDER BY VoteCount

If you add additional entries columns to the SELECT list, be sure to add them to the GROUP BY list as well.

---

Addendum: @JuanPabloCalifano pointed out, correctly, that this query won't include entries with zero votes. Here's how to include them:

SELECT
  entries.page_id,
  COALESCE(COUNT(votes.id), 0) AS VoteCount
FROM entries
LEFT JOIN votes ON entries.media_id = votes.media_id
GROUP BY entries.page_id
ORDER BY VoteCount