Custom query: Get posts by one custom field, ORDER BY another custom field? (1 post)

  1. mendezki
    Posted 7 years ago #

    For an event calendar I'm retrieving posts from a certain category by custom fields. The start date custom field name is start-date, end date, you guessed it: end-date

    It retrieves posts with an end date >= now, and should order the output by the end date.

    I'm using the query below. There's 2 LEFT JOIN's in there to provide the value for the start and end date. Is there a better way to do this?

    $querystr = "
    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta AS eventend ON(
    $wpdb->posts.ID = eventend.post_id
    LEFT JOIN $wpdb->postmeta AS eventstart ON(
    $wpdb->posts.ID = eventstart.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.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id = 23
    AND $wpdb->posts.post_status = 'published'
    AND eventend.meta_key = 'event-end'
    AND eventend.meta_value >= NOW()
    AND eventstart.meta_key = 'event-start'
    ORDER BY eventstart.meta_value ASC
    $eventlist = $wpdb->get_results($querystr, OBJECT);

Topic Closed

This topic has been closed to new replies.

About this Topic