Over the past few months, I’ve been working on the new website for The Tech Upload. They launched Phase I about a month ago, and I’ve been trying to finish up Phase II for them. Part of Phase II includes a reddit-like user posting section, with the ability for visitors to vote posts up/down based on how useful/informative it is. Since the site is run by WordPress on the backend, I decided to use some plugins rather than write the functionality from scratch. After some searching, I decided to use the UpDown UpDown plugin to provide the voting functionality, and the Post from Site plugin to keep users out of the backend. Once I had UpDown UpDown working properly on the site, I realized that it didn’t provide any way to sort the posts by votes. I googled around a little bit to find the proper WordPress function to allow a custom query, and came up with
$wpdb->get_results(). The query would need to return the published posts for a specific category, ordered by the total number of votes the post has received. With the post being stored in one table, the category information in another, and the votes in a third, it took several attempts to craft the appropriate query.
1 2 3 4 5 6 7 8 9 10 11 12 13
$query = " SELECT p.*, IFNULL((votes.vote_count_up - votes.vote_count_down),0) as PostVote FROM $wpdb->posts as p LEFT JOIN `wp_up_down_post_vote_totals` as votes ON p.ID = votes.post_id WHERE p.ID IN (SELECT tr.`object_id` FROM $wpdb->term_relationships as tr WHERE tr.`term_taxonomy_id` = '##') AND p.post_status = 'publish' AND p.post_type = 'post' ORDER BY PostVote DESC "; $posts = $wpdb->get_results($query, OBJECT);
p is aliased as the
posts table – all of the post information is returned from here. PostVote is the total of up and down votes for each post from the
wp_up_down_post_vote_totals, aliased as votes.
IFNULL() is used in the event that a post has no vote data stored in the table, and avoids having a NULL result returned. This is also necessary for ordering the results.
Set up the alias and determine which table is being queried.
In order to not lose any posts that don’t have any vote information, a
LEFT JOIN is used to select all of the posts. This line allows the vote totals to be returned properly.
Since only one category needs to be queried, this line makes sure that all of the posts are part of the appropriate category.
IN is used as an array of Object-ID’s are returned containing all of the posts in the selected category. Replace ‘##” with the taxonomy_id, not the category ID, of the appropriate category.
The last part of the
WHERE clause is to ensure that the posts returned are of the ‘post’ type and have been published already.
Finally, the posts are ordered by the PostVote, to ensure that the highest voted posts are at the top.
I hope this is helpful to anyone who was in the same position as me. Any questions, leave a comment below.