WordPress.org

Forums

Sorting by meta AND category (10 posts)

  1. scrambled
    Member
    Posted 3 years ago #

    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); ?>
  2. scrambled
    Member
    Posted 3 years ago #

  3. Marventus
    Member
    Posted 3 years ago #

    Hi there!
    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.
    Cheers!

  4. scrambled
    Member
    Posted 3 years ago #

    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?

  5. bcworkz
    Member
    Posted 3 years ago #

    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.

  6. scrambled
    Member
    Posted 3 years ago #

    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)
  7. bcworkz
    Member
    Posted 3 years ago #

    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.

  8. jaket rocket an.com
    Member
    Posted 3 years ago #

    [Code moderated as per the Forum Rules. The maximum number of lines of code that you can post in these forums is ten lines. Please use the pastebin]

  9. scrambled
    Member
    Posted 3 years ago #

    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.

    http://pastebin.com/vJ9sfJym

  10. scrambled
    Member
    Posted 3 years ago #

    To clarify, I am confused about how the left joins work.

Topic Closed

This topic has been closed to new replies.

About this Topic