WordPress SQL order by subquery

I have follow SQL query for events search in WordPress

SELECT post_id, post_title, guid, (SELECT meta_value FROM wp_postmeta WHERE meta_key = 'datefrom' AND wp_postmeta.post_id = wp_posts.ID LIMIT 1) as datefrom
FROM wp_posts
INNER JOIN wp_term_relationships txr1 ON wp_posts.ID = txr1.object_id
INNER JOIN wp_term_taxonomy tx1 ON txr1.term_taxonomy_id = tx1.term_taxonomy_id
INNER JOIN wp_terms trm ON tx1.term_id = trm.term_id
INNER JOIN wp_term_relationships txr2 ON wp_posts.ID = txr2.object_id
INNER JOIN wp_term_taxonomy tx2 ON txr2.term_taxonomy_id = tx2.term_taxonomy_id
INNER JOIN wp_terms trk ON tx2.term_id = trk.term_id
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( ( $wpdb->postmeta.meta_key LIKE 'venues_%_eventdates_%_date' AND CAST($wpdb->postmeta.meta_value AS CHAR) = '" . $date . "' ) )
AND ( tx1.taxonomy= 'events_category' AND ( trm.term_id = '" . $category . "'))
AND ( tx2.taxonomy= 'events_where' AND ( trk.term_id = '" . $where . "'))
AND wp_posts.post_type = 'events'
AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value ASC

problem is in ordering date in subquery, sometimes postmeta “venues_%eventdates%date” is blank, so i need include postmeta “datefrom”. I found this thread on stackoverflow, but for me is too hard figure out. So I need order postmeta datefrom and also “venues%eventdates%_date” onetime.

in json result

$list = $wpdb->get_results($event_sql);
$response = json_encode( array( 'success' => true, 'results' => $list ) );

I get this

Object {post_id: "3868", post_title: "Special event", guid: "http://website/event/my-special/", dateorigin: null, datefrom: "29.5."}

sometimes

Object {post_id: "736", post_title: "Classic concert", guid: "http://website/event/classic-concert/", dateorigin: "15.5.", datefrom: "13.5."}

priority is postmeta venues_%eventdates%_date

Thank you for help


Source: wpdb

Leave a Reply