UpDown UpDown Sorting

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);

Line 1:
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.

Line 2:
Set up the alias and determine which table is being queried.

Line 3-4:
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.

Line 6-8:
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.

Line 9-10:
The last part of the WHERE clause is to ensure that the posts returned are of the ‘post’ type and have been published already.

Line 11:
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.

This entry was written by Marc Budofsky , posted on Thursday March 08 2012at 10:03 pm , filed under Programming, Tutorial . Bookmark the permalink . Post a comment below or leave a trackback: Trackback URL.

8 Responses to “UpDown UpDown Sorting”

  • Jill says:

    Thank you for this! One question: Is it possible to make the taxonomy_ID section dynamic, so that the function will update itself depending on the taxonomy_ID of the post being commented on? Right now it looks like your query only allows for hardcoding the taxonomy_id, is that correct?

  • Jill –

    I’m glad you found the blog! As far as you’re question, I was using that query on a category page, so it would display the posts based on their vote ranking. If you wanted to do it dynamically, based on a loaded post, I think you should be able to query the post in question and pull its category. From there, you would need to do a reverse look-up of the taxonomy ID, and then store that in a variable. Finally, use the variable that has the dynamic taxonomy ID and inject that into the SQL query. I hope that answers your question – if not, I’m not than happy to take another shot at it!

    Marc

  • Tiago says:

    Howdy! thanks so much for this! i am using the same plugin on my website and would love to have this implanted! it’s exactly what i need. I just have one problem… In don’t know where on the code to add this. I am using WordPress as well, so many folders etc… i am not to good at php. But i got a friend who is willing top help me out. But he also has no clue where to start digging hehe.

    cheers!
    Tiago

  • Pedro Rivera says:

    Hey Marc. A friend is trying to do exactly the same thing and we have a question: where should the code be inserted?

    Thanks!

  • @Tiago & Pedro –

    Great to see other people benefiting from this post! The code will need to go into the theme template files. Unfortunately, since every theme is a little different, there’s no definitive answer as to which file you’ll need to modify. If you’re using a standard(-ish) theme, I believe you’ll want to put the query into the `page.php` file right before the while() loop that processes each post. If you have any other questions, let me know!

    Marc

  • Tiago says:

    Thanks for your answer Marc! i read what you posted on lines 1-11 and noticed i need to find a taxonomy_id, not the category ID, of the appropriate category. What is this? i have searched around the theme folder and have only found taxonomy_gallerie.php ?

    I will try and add it to page.php and see how it goes.
    cheeers!

  • `taxonomy_id` is a database field – try using phpmyadmin as a GUI to manage the database and you can find the details there. The `taxonomy_id` is used for the category/tags assigned to a post.

  • Tiago says:

    thanks again! i will look in to it. I don’t have much knowledge regarding php.. Where most likely would i find the taxonomy_id ?

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>