I am trying to use the wordpress wpdb class to return custom posts with one of two values for meta keys and then sort those posts by a third meta value. I can get the posts that I want but I just can’t seem to figure out how to sort them the way I would like.
The function I have so far is:
function artists_search_country($country_alt){
global $wpdb;
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND ((wpostmeta.meta_key = '_artist_country' AND wpostmeta.meta_value = '$country_alt')
OR (wpostmeta.meta_key = '_artist_country_sub' AND wpostmeta.meta_value = '$country_alt'))
AND wposts.post_type = 'artists'
AND wposts.post_status = 'publish'
";
$myposts = $wpdb->get_results($querystr, OBJECT);
return $myposts;
}
I would like to do something like:
function artists_search_country($country_alt){
global $wpdb;
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND ((wpostmeta.meta_key = '_artist_country' AND wpostmeta.meta_value = '$country_alt')
OR (wpostmeta.meta_key = '_artist_country_sub' AND wpostmeta.meta_value = '$country_alt'))
AND wposts.post_type = 'artists'
AND wposts.post_status = 'publish'
ORDER BY (wpostmeta.meta_key = '_artist_artist_last_name' AND wpostmeta.value) ASC
";
$myposts = $wpdb->get_results($querystr, OBJECT);
return $myposts;
}
Please excuse the poor syntax on the ORDER BY
line but I just don’t know how to approach this one, everything I try breaks the query. Any help would be greatly appreciated as I don’t have that much experience with SQL queries.
Try this SQL statement:
You need to make a second join for the artist’s last name.
Another recommendation is to leverage WordPress’ $wpdb->prepare, like this:
Hope this helps.