Sorting by meta AND category
The code below makes an “events list” by sorting ALL POSTS with something in the 05/09/2012 format in the meta tag in chronological order. How can I tell it to only look at a category? So I could create second events list?
<?php // Build a custom query to get posts from future dates. $querystr = " SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'Date' AND STR_TO_DATE(wpostmeta.meta_value,'%m/%d/%Y <span class=\"hide\">%H:%i</span>') >= CURDATE() AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY STR_TO_DATE(wpostmeta.meta_value,'%m/%d/%Y <span class=\"hide\">%H:%i</span>') ASC LIMIT 200 "; $events = $wpdb->get_results($querystr, OBJECT); if ($events): foreach ($events as $post): global $post; setup_postdata($post); ?>
But I don’t think that code works with WP 3.3.2. Help?
There are several event plugins out there, so no need to reinvent the wheel here, ;-P.
One that I use frequently and tend to recommend is the Event Calendar plugin.
We have a custom setup and want to keep it like this. Thought it would be easy to just add a category? Can anyone point me in the right direction?
You need to add a few joins to your query so you can access the category data in terms_taxonomy and add another condition. Displaying_Posts_Using_a_Custom_Select_Query#Query_based_on_Custom_Field_and_Category has an example on how this is done.
Do I need all three of those joins to only sort posts in one category?
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id) LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
Seems ridiculous, but yes. You can only join tables that have columns in common, resulting in the join chain you see. It’s a pain to write queries for, but SQL handles it very quickly, so it’s not a big deal once you get the query right.
So I tried this. I am using a piece of existing code. Is the first section consistent? This is my first time ever coding something this complex.
I tried to annotate the code below with capital letters.
- The topic ‘Sorting by meta AND category’ is closed to new replies.