Custom SELECT Query With Multiple Meta Values (Ordering Problem)

  • Resolved slaterino

    (@slaterino)


    12 years, 6 months ago

    Hi,
    I have two custom meta values that I am using for an events list and have almost got everything working perfectly except for the ordering. I have two dates (event_date and event_enddate). One of these is a start date which is always present, and the other is an end date which is only used when needed. I have created a query which lists all dates where either of these fields are equal or greater to today’s date, which works fine. However, I want all of the events to be ordered solely by the event_date field. How can I do this? I’ve spent all day trying to do this and just can’t work it out!

    Here’s my code:

    SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
LEFT JOIN $wpdb->term_relationships ON (wposts.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 ((wpostmeta.meta_key = 'event_enddate' AND wpostmeta.meta_value >= CURDATE())
 OR (wpostmeta.meta_key = 'event_date' AND wpostmeta.meta_value >= CURDATE()))
AND wposts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN(15)
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC
Viewing 8 replies - 1 through 8 (of 8 total)

  • Use two different joins on the postmeta table, one for the event_date and another for the event_enddate. Give them different aliases and sort only on the meta_value from the event_date one.

    Thread Starter slaterino

    (@slaterino)

    How would I do that? All the examples I’ve looked at have specified the meta key in the WHERE clause. How would I specify event_date in the LEFT JOIN? I haven’t seen anyone do anything like that in the examples I’ve looked at.

    I don’t have a way to easily test this, but I think this is what you want:

    SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts
INNER JOIN $wpdb->postmeta wpm1 ON (wposts.ID = wpm1.post_id
                       AND wpm1.meta_key = 'event_date'
                       AND wpm1.meta_value >= CURDATE())
LEFT JOIN $wpdb->postmeta wpm2 ON (wposts.ID = wpm2.post_id
                       AND wpm2.meta_key = 'event_enddate'
                       AND wpm2.meta_value >= CURDATE())
LEFT JOIN $wpdb->term_relationships ON (wposts.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 wposts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN(15)
ORDER BY wpm1.meta_value ASC
    Thread Starter slaterino

    (@slaterino)

    Hey vtxyzzy,
    Thanks for this. It cures one problem but starts another.

    At the moment the events where the ‘end_date’ value is less than today’s date but the ‘event_enddate’ is greater than today’s date are not showing up. I’m presuming this is because the INNER JOIN is causing this to happen. In the original query I was able to add an OR statement in the WHERE clause but I’m less good at working out how to cure this situation with the JOINS.

    Any ideas?

    Try changing the INNER JOIN to a LEFT JOIN.

    Thread Starter slaterino

    (@slaterino)

    I tried it. When I do it pretty much brings up every record in the database!

    One last try:

    SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->postmeta wpm1 ON (wposts.ID = wpm1.post_id
                       AND wpm1.meta_key = 'event_date')
LEFT JOIN $wpdb->postmeta wpm2 ON (wposts.ID = wpm2.post_id
                       AND wpm2.meta_key = 'event_enddate')
LEFT JOIN $wpdb->term_relationships ON (wposts.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
((wpm1.meta_value >= CURDATE()) OR (wpm2.meta_value >= CURDATE()))
AND wposts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN(15)
ORDER BY wpm1.meta_value ASC
    Thread Starter slaterino

    (@slaterino)

    Dude, vtxyzzy, whoever you are, you’re a genius! I really should have thought of that! I think I’d probably given up, or at least my brain had. So many thanks for your help. I think you’ve changed my life!

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Custom SELECT Query With Multiple Meta Values (Ordering Problem)’ is closed to new replies.