Plugin or theme that sort posts as 'top this year' and 'top last month"?

Question

Is there any free WordPress plugin or theme that sort posts as 'top this year' and 'top last month" or 'top 24 hours' based on voting?

I'm using the Vote It Up plugin right now. But it doesn't have the capability of sorting post as a loop.

Any suggestions?

Answer 1

Try GD Star Rating - I'm pretty sure it stores vote times and so can be used to do this - you'll probably need to extract the ordering yourself though.

http://www.gdstarrating.com/

Answer 2

Until the plugin switches to storing votes differently there's going to be no nice way to sort based on votes.

Take a look at a row from the votes table.

+-------------------------------------------------------+
| ID | post |  votes  | guests | usersinks | guestsinks |
+-------------------------------------------------------+
| 1  | 1234 | ,1,2,3  |        |           |            |
+-------------------------------------------------------+

All the votes for users are being stored in a singular column(votes), and MySQL has no proper way to split, count and order based on that column.

Perhaps some MySQL ninja can create a procedure/function to do it, but that's beyond my SQL know-how.

Not sure why the author choose to store votes that way, but from a result sorting perspective that's a poor DB design decision(imo).

I appreciate i havn't really addressed the question, but thought it might be nice to point out while it may be something that is easy in theory, there's a good reason the author hasn't provided this facility with the plugin already - his db design makes sorting based on vote extremely hard(at the SQL level at least).

This is what i'd do if i was writing such a plugin..

Use one new table for holding just the votes, all votes..

Here's some imaginary data in the table.

Table: wp_post_votes
Type: new
+--------------------------------------------+
|  ID    |  post_id |  user_id  |  type      |
+--------------------------------------------+
|  1     |  123     |   10      |  up        |
|  2     |  123     |   22      |  down      |
|  3     |  123     |   4       |  up        |
|  4     |  123     |   1       |  up        |
|  5     |  25      |   6       |  up        |
|  6     |  25      |   10      |  up        |
|  7     |  25      |   1       |  down      |
|  8     |  25      |   2       |  up        |
+--------------------------------------------+

Alongside that i'd use usermeta and postmeta to store vote counts for both the posts and users voting.

Here's some imaginary meta data to help visualize.

Table: wp_postmeta
Type: core, existing
+-----------------------------------------------------------+
|  meta_id  |  post_id   |   meta_key   |   meta_value      |
+-----------------------------------------------------------+
|  512      |   123      |  vote_count  |        4          |
|  513      |   25       |  vote_count  |        4          |
+-----------------------------------------------------------+

Table: wp_usermeta
Type: core, existing
+-----------------------------------------------------------+
|  umeta_id  |  user_id  |  meta_key   |  meta_value        |
+-----------------------------------------------------------+
|  443       |   1       |  vote_count |       2            |
|  445       |   2       |  vote_count |       1            |
|  442       |   4       |  vote_count |       1            |
|  444       |   6       |  vote_count |       1            |
|  440       |   10      |  vote_count |       2            |
|  441       |   22      |  vote_count |       1            |
+-----------------------------------------------------------+

That way the posts could be sorted based on meta value and additionally leveraging the meta tables would mean quick access to update and fetch functions, like get_user_meta, get_post_meta, update_post_meta ... and so on..(i'm sure you get the point).

Just thinking out loud, because your question had me thinking about how i'd do it differently..