help? – Custom SQL Query. How to ORDER BY two fields?

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?

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

Secondly: Your posts are listed here: https://www.ads-software.com/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

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.

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

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?

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.

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.

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

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.

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

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!

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?

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.

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.

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!