Mysql one to many relationship. Order by number of rows in second table

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!

The problem is that I want to get the records in the entries table ordered by the number of records with that media_id in the votes table. — Cobradelaplaya, Aug 06 '13 at 12:17

Ed Gibbs · Accepted Answer · 2013-08-06T12:47:08.810

4

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

edited Aug 06 '13 at 12:47

answered Aug 06 '13 at 12:18

Ed Gibbs

25,924
4
46
69

Excellent! Worked perfect with a little modification: SELECT * , COUNT( * ) AS VoteCount FROM insta_entries INNER JOIN insta_votes ON insta_entries.media_id = insta_votes.media_id GROUP BY insta_entries.media_id ORDER BY VoteCount – Cobradelaplaya Aug 06 '13 at 12:26
2

Bear in mind that if you do an inner join, entries without votes won't be listed. I think you need a left join here. – Juan Pablo Califano Aug 06 '13 at 12:32
1

Good catch @JuanPabloCalifano! I really do know better than that :) Cobradelaplaya - if you need to include entries with zero votes please see the addendum to my answer. – Ed Gibbs Aug 06 '13 at 12:39
1

@Ed Gibbs. Thanks. I don't mean to nitpick but the group by should be done separately. Otherwise COUNT(*) will return always 1 (since it'll be counting the number of rows after they've been grouped). – Juan Pablo Califano Aug 06 '13 at 12:45
1

That's not nitpicking because we're talking about an actual error here, and *really* I know better :) I've just fixed the problem by changing from `COUNT(*)` to `COUNT(votes.id)`. Time for more coffee... – Ed Gibbs Aug 06 '13 at 12:49
Great! By the way, I thought you needed to do a subquery but now I see in your solution that you just need to count the other column to get the right value. – Juan Pablo Califano Aug 06 '13 at 12:52
I love this solution! This is more elegant than a subquery. – enchance Jun 15 '16 at 11:40

score 0 · Answer 2 · edited Aug 06 '13 at 12:35

0

SELECT COUNT(*) as CNT, `media_id` FROM `insta_votes` GROUP BY `media_id` order by 1;

edited Aug 06 '13 at 12:35

Praveen Prasannan

7,093
10
50
70

answered Aug 06 '13 at 12:16

Mordechai

718
1
8
23

score 0 · Answer 3 · edited Aug 06 '13 at 12:38

0

SELECT COUNT(*), media_id FROM insta_votes 
GROUP BY media_id 
ORDER BY COUNT(*);"

edited Aug 06 '13 at 12:38

Danilo Piazzalunga

7,590
5
49
75

answered Aug 06 '13 at 12:17

Praveen Prasannan

7,093
10
50
70

Mysql one to many relationship. Order by number of rows in second table

3 Answers3