WordPress.org

1. ninjaboy81
   Member
   Posted 5 years ago #
   

   This is the custom select query that ALMOST works perfectly (taken from this thread http://wordpress.org/support/topic/121011?replies=11):

   SELECT * FROM $wpdb->posts
   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)
   WHERE $wpdb->term_taxonomy.term_id = 3
   AND $wpdb->term_taxonomy.taxonomy = 'category'
   AND $wpdb->posts.post_status = 'publish'
   AND $wpdb->postmeta.meta_key = 'Event_Date'
   ORDER BY $wpdb->postmeta.meta_value ASC

   ^ this almost works for my purposes... but how can it be modified to ORDER BY 2 custom fields instead of just one?

   I've got things setup so that every post in category 3 has an Event_Date custom field and an Event_Time custom field.

   Right now it orders the post titles by the Event_Date.
   I would like it to order also by Event_Time like this:

   December 25th
   eventA 8pm
   eventB 9pm
   December 26th
   eventC 11am
   eventD 3pm
   eventE 8pm
   December 31st
   eventF 12pm
   eventG 6pm

   Also, how would I get it to only list the first 5 posts found instead of ALL posts?

2. ninjaboy81
   Member
   Posted 5 years ago #
   

   wow. this place is hard to get a reply from.
   and i can't seem to find an easy way to see my recent posts.

   anyone know how to see all your own posts?

3. Samuel Wood (Otto)
   Tech Ninja
   Posted 5 years ago #
   

   Hard to get a reply from? You only waited 16 minutes.

   Secondly: Your posts are listed here: http://wordpress.org/support/profile/359795

   Third: This might work.

   SELECT * FROM $wpdb->posts
   LEFT JOIN $wpdb->postmeta AS eventdate ON(
   $wpdb->posts.ID = $wpdb->postmeta.post_id
   AND $wpdb->postmeta.meta_key = 'Event_Date'
   )
   LEFT JOIN $wpdb->postmeta AS eventtime ON(
   $wpdb->posts.ID = $wpdb->postmeta.post_id
   AND $wpdb->postmeta.meta_key = 'Event_Time'
   )
   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)
   WHERE $wpdb->term_taxonomy.term_id = 3
   AND $wpdb->term_taxonomy.taxonomy = 'category'
   AND $wpdb->posts.post_status = 'publish'
   ORDER BY eventdate.meta_value, eventtime.meta_value ASC

4. ninjaboy81
   Member
   Posted 5 years ago #
   

   I really appreciate your help! Actually this is the first time i've gotten a response and i've posted other questions days ago. :( I thought maybe no one likes new people haha. but really, i apprecaite your help.

   The above query doesn't produce any posts at all actually. :(

   My loop goes to my "Sorry, no posts found" else case.

   --

   you've got all the names right. my custom fields are Event_Date and Event_Time just like you have there.

   I think you're trying to create aliases there but I don't know enough about SQL to trouble shoot it on my own.

5. Samuel Wood (Otto)
   Tech Ninja
   Posted 5 years ago #
   

   Hmm.. Try this.

   SELECT * FROM $wpdb->posts
   LEFT JOIN $wpdb->postmeta AS eventdate ON(
   $wpdb->posts.ID = eventdate.post_id
   AND eventdate.meta_key = 'Event_Date'
   )
   LEFT JOIN $wpdb->postmeta AS eventtime ON(
   $wpdb->posts.ID = eventtime.post_id
   AND eventtime.meta_key = 'Event_Time'
   )
   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)
   WHERE $wpdb->term_taxonomy.term_id = 3
   AND $wpdb->term_taxonomy.taxonomy = 'category'
   AND $wpdb->posts.post_status = 'publish'
   ORDER BY eventdate.meta_value, eventtime.meta_value ASC

6. ninjaboy81
   Member
   Posted 5 years ago #
   

   That seems to do it.. I just changed this: ORDER BY eventdate.meta_value DESC, eventtime.meta_value DESC

   (you're amazing, and thank you!)

   ...

   I have a concern about the logic though.. when it's sorting the dates.. how is it suppose to know that Sunday comes before Monday? What if it's sorting alphabetically instead of Chronologically?

   Is there a way to specify that these values should be treated as DATE data types?

7. ninjaboy81
   Member
   Posted 5 years ago #
   

   Yup, my fears were realized. Here are my results from the query:

   Tuesday, April 8th, 2008
   eventA
   eventB
   Sunday, April 20th, 2008
   eventC
   eventD
   Saturday, April 19th, 2008
   eventE

   ^ Sunday April 20th should be AFTER Saturday April 19th.
   So, it seems it is arranging the values based on something other than the Date type value.

8. Samuel Wood (Otto)
   Tech Ninja
   Posted 5 years ago #
   

   The meta-values are strings. If you're putting in "Tuesday, April 8th, 2008", then yes, it will be incorrect. You need to put in dates that are correctly sortable, like "2008-04-08". When you display that information, you can run it through strtotime and strftime to output it in a different format. But MySQL isn't going to magically know that that string is a date and sort appropriately.

9. ninjaboy81
   Member
   Posted 5 years ago #
   

   Got a chance to try this logic tonight and it works.
   Thanks for your help, Otto42.

10. mendezki
    Member
    Posted 4 years ago #
    

    Thanks a lot for the example, Otto!

    Since I'm using those meta keys on any post or page, turning them into event calendar items, I found I needed to move

    AND eventdate.meta_key = 'Event_Date'

    and

    AND eventtime.meta_key = 'Event_Time'

    down like in the example below, or all posts would show:

    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta AS eventdate ON(
    $wpdb->posts.ID = eventdate.post_id
    )
    LEFT JOIN $wpdb->postmeta AS eventtime ON(
    $wpdb->posts.ID = eventtime.post_id
    )
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    WHERE $wpdb->posts.post_status = 'publish'
    AND eventdate.meta_key = 'Event_Date'
    AND eventtime.meta_key = 'Event_Time'
    ORDER BY eventdate.meta_value, eventtime.meta_value ASC

    Thought it might come in handy for passers-by.

11. killiantobin
    Member
    Posted 4 years ago #
    

    Thanks for the help so far, this thread has been very helpful.
    I am also trying to use WP as an event listing. I have two custom fields I want to use to filter and sort the postings: startdate and enddate.

    The filtering seems to work and so does the sorting, but I am getting 3 of each posting returned when I run the loop.

    Can anyone point out where I am going wrong here?

    Thanks!

    <?php 
    
    	$querystr = "
    		SELECT * FROM $wpdb->posts
    		LEFT JOIN $wpdb->postmeta AS startdate ON(
    		$wpdb->posts.ID = startdate.post_id
    		AND startdate.meta_key = 'startdate'
                    )
    		LEFT JOIN $wpdb->postmeta AS enddate ON(
    		$wpdb->posts.ID = enddate.post_id
    		AND enddate.meta_key = 'enddate'
    		)
    		LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    		WHERE $wpdb->posts.post_status = 'publish'
    		AND enddate.meta_value > CURDATE()
                    AND startdate.meta_value <= DATE_ADD(CURDATE(),INTERVAL 30 DAY)
                    ORDER BY enddate.meta_value, startdate.meta_value ASC
    	";

12. killiantobin
    Member
    Posted 4 years ago #
    

    Nevermind!
    I think I fixed it by removing the line:

    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)

    It seems happy!

13. jns120
    Member
    Posted 4 years ago #
    

    I can't for the life of me get this working. I actually want to get posts based on category id and also a specific meta value for a meta key called event_date. Then I want to sort by another meta value for a different key called event_time. I thought this should do it but no luck.
    

    $querystr = "SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta AS eventtime ON($wpdb->posts.ID = $wpdb->postmeta.post_id = eventtime.post_id AND eventtime.meta_key = 'event_time')
    LEFT JOIN $wpdb->postmeta AS eventdate ON($wpdb->posts.ID = $wpdb->postmeta.post_id = eventdate.post_id AND eventdate.meta_key = 'event_date')
    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)
    WHERE $wpdb->term_taxonomy.term_id = 8
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND eventdate.meta_value = $new_date
    ORDER BY eventtime.meta_value ASC
    "; 
    
    $results = $wpdb->get_results($querystr);

    Any thoughts?

14. Dan Butcher
    Member
    Posted 4 years ago #
    

    I've got a similar issue, and I can't figure out how to adapt the code above to my situation. I've been using this code to generate a list of upcoming courses:
    

    $querystr = "
        SELECT * FROM $wpdb->posts
    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)
    WHERE $wpdb->term_taxonomy.term_id = 22
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'course'
    ORDER BY $wpdb->postmeta.meta_value ASC
    
        "

    This produced a list of courses ordered by number, and it worked well as long as I wanted to list only 1 semester at a time.

    Now I need to list courses for 2 semesters, so I've added a key called "term" that will have the value "spring," "summer," or "fall" (or I could number the terms 1, 2, and 3 if that would simplify things).

    So what I want to do is first filter the list by term, and then display sorted by course number. I can't figure out how to add in the code to filter by term.

15. Dan Butcher
    Member
    Posted 4 years ago #
    

    I solved it by changing the code to this:
    

    $querystr = "
        SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta AS term ON(
    $wpdb->posts.ID = term.post_id
    AND term.meta_key = 'term'
    )
    LEFT JOIN $wpdb->postmeta AS course ON(
    $wpdb->posts.ID = course.post_id
    AND course.meta_key = 'course'
    )
    WHERE $wpdb->posts.post_status = 'publish'
    AND term.meta_value = 'summer'
    ORDER BY course.meta_value ASC
        ";

    I removed the filter by category, since only my course descriptions carry the custom keys of term and course.

16. artsaround
    Member
    Posted 4 years ago #
    

    Hi Dan,

    Thanks for your code snippet, I think it's what I'm looking for. I'm trying to restrict and then order a number of posts that have metadata for species and genus. I tried replacing all the word 'term' with 'species' and 'course' with 'genus' in your script. However I can't get the generated page to display and records. Any idea what I could be doing wrong? Seems like it should be pretty clear cut.

    In my example I would restrict the results to a certain species (ie your summer) and then order by genus (ie your course).

    Cheers!

17. artsaround
    Member
    Posted 4 years ago #
    

    Hi, I'm not sure what I changed but I have it working. I got to thinking though, what I'd really like to be able to do is modify the custom field restrictions on the fly instead of hard coding them into the page template.

    Here's what I have right now:

    <?php
    	 $restriction1 = 'geographic_area';
    	 $restriction2 = 'species';
    	 $restriction3 = 'donor';
    
    	 $value1 = 'India';
    	 $value2 = 'Calisaya';
    	 $value3 = 'India Museum';
    
    	 $querystr = "
    		SELECT * FROM $wpdb->posts
    		LEFT JOIN $wpdb->postmeta AS $restriction1 ON(
    		$wpdb->posts.ID = $restriction1.post_id
    		AND $restriction1.meta_key = '$restriction1'
    		)
    		LEFT JOIN $wpdb->postmeta AS $restriction2 ON(
    		$wpdb->posts.ID = $restriction2.post_id
    		AND $restriction2.meta_key = '$restriction2'
    		)
    		LEFT JOIN $wpdb->postmeta AS $restriction3 ON(
    		$wpdb->posts.ID = $restriction3.post_id
    		AND $restriction3.meta_key = '$restriction3'
    		)
    		WHERE $wpdb->posts.post_status = 'publish'
    		AND $restriction1.meta_value = '$value1'
    		AND $restriction2.meta_value = '$value2'
    		AND $restriction3.meta_value = '$value3'
    		ORDER BY species.meta_value ASC
    	 	";
    
    	 $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    	?>

    Here's what I was thinking. You create a page, linked to a page template with a standard loop and then following that, the querystr following Dan's example above. Instead of coding the restrictions into the template the template gets those values from the metadata associated with the newly created page.

    So each page would have 6 custom fields (restriction_key_1, restriction_value_1, etc) that would link into the following:

    $restriction1 = 'restriction_key_1';
    	 $restriction2 = 'restriction_key_2';
    	 $restriction3 = 'restriction_key_2';
    
    	 $value1 = 'restriction_value_1';
    	 $value2 = 'restriction_value_2';
    	 $value3 = 'restriction_value_3';

    The only problem is I'm not sure how to call those metadata fields from the post and insert them into place.

    Does anyone have an idea?

18. Dan Butcher
    Member
    Posted 4 years ago #
    

    artsaround, I'm glad you found the code helpful--but you've quickly moved beyond my knowledge!

    I will add, since others might like to know, that I displayed the results of the query using a table, like this; this loop follows immediately after the query:

    $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    ?>
     <?php if ($pageposts): ?>
    
    <table id="tablestripe" width="100%" border="0" cellspacing="0" cellpadding="0">
    		<tr>
    			<th width="12%">Number</th>
    			<th width="45%">Title</th>
    			<th width="15%">Faculty</th>
    			<th width="28%">Day/Time</th>
    		</tr>
      <?php foreach ($pageposts as $post): ?>
        <?php setup_postdata($post); ?>
    		<tr>
    			<td class="coursenumber" valign="top" width="12%">
                                    <?php $key="course"; echo get_post_meta($post->ID, $key, true); ?>
                            </td>
    			<td class="coursetitle" valign="top" width="45%"><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></td>
    
    			<td class="coursefaculty" valign="top" width="15%"><?php $key="instructor"; echo get_post_meta($post->ID, $key, true); ?></td>
    			<td class="coursetime" valign="top" width="28%"><?php $key="time"; echo get_post_meta($post->ID, $key, true); ?></td>
    		</tr>
      <?php endforeach; ?>
      <?php else : ?>
    <p><?php _e('Sorry, no posts matched your criteria.'); ?></p>
    
    <?php endif; ?>
    	</table>

    You can see the page in action here.

Topic Closed

This topic has been closed to new replies.