Code: Select all
$mainquery = "SELECT $wpdb->posts.*, $wpdb->postmeta.*
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta
ON $wpdb->posts.ID = $wpdb->postmeta.post_id
WHERE $wpdb->postmeta.meta_key='Excerpt'
AND $wpdb->posts.post_parent = '24'
AND $wpdb->posts.ID IN (
SELECT post_id
FROM $wpdb->postmeta
WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%'
AND $wpdb->postmeta.meta_key='Surname'
ORDER BY $wpdb->postmeta.meta_value ASC
)
";
To give a bit of context: there are 2 tables. posts and posts_meta. Each post has a meta_key 'Excerpt' and a meta_key 'Surname'. So what the purpose is of this query is first search the posts_meta for a Surname with a certain letter. This subquery returns a list of postID's. Then the main query returns some data from the posts and the meta data Excerpts belonging to those posts.
Is it somehow possible to use the order of the IN clause to get the same order in the end result?
Or can I rewrite the whole query in such a way that I get the same effect?
I also tried:
Code: Select all
$mainquery = "SELECT DISTINCT $wpdb->posts.ID, $wpdb->postmeta.*
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta
ON $wpdb->posts.ID = $wpdb->postmeta.post_id
WHERE $wpdb->posts.post_parent = '24'
AND $wpdb->posts.ID IN (
SELECT post_id
FROM $wpdb->postmeta
WHERE $wpdb->postmeta.meta_value LIKE '". $safesearchletter ."%'
AND $wpdb->postmeta.meta_key='Surname'
ORDER BY $wpdb->postmeta.meta_value ASC
)
";