xRobot xRobot - 2 months ago 7
MySQL Question

How to order entries by vote?

These are my tables:

CREATE TABLE IF NOT EXISTS `entries` (
`id` int NOT NULL AUTO_INCREMENT,
`title` text,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


id title date
1 aaa 2016-09-15 19:00:00
2 bbb 2016-09-14 19:00:00
3 ccc 2016-09-13 19:00:00
4 ddd 2016-09-12 19:00:00
5 eee 2016-09-11 19:00:00


CREATE TABLE IF NOT EXISTS `votes` (
`id` int NOT NULL AUTO_INCREMENT,
`id_entry` int,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

id id_entry
1 1
2 1
3 1
4 2
5 3


I need a way to order all entries by vote and then by date, So I wrote this query:

SELECT COUNT(entries.id) as totvotes, entries.id, entries.date FROM entries LEFT JOIN votes ON entries.id=votes.id_entry GROUP by entries.id ORDER by totvotes DESC, entries.date DESC


And the result is this:

totvotes entries.id entries.date
3 1 2016-09-15 19:00:00
1 2 2016-09-15 19:00:00
1 3 2016-09-15 19:00:00
1 4 2016-09-15 19:00:00
1 5 2016-09-15 19:00:00


As you can see, the query show totvotes=1 for entries without vote ( 4 and 5 ). How can I avoid that ?

Answer

Just count votes.id_entry instead of this SELECT COUNT(entries.id).

So try with this: SELECT COUNT(votes.id_entry) as totvotes...

Some subtleties regarding COUNT:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE

Comments