This isn’t really a WordPress question as it’s just standard SQL, so it doesn’t really belong here, but I have some points to make about your use of $wpdb->prepare()
so will answer the question.
So, let’s look at your WHERE
clause, isolated, with values substituted, and indented to reflect how it will be interpreted:
WHERE
pm.meta_key = "select_analyst" AND p.post_status = "new" OR
p.post_status = "pending_review" AND p.post_type = "post_type"
This is returning meta_value
for rows that meet the following conditions:
meta_key
isselect_analyst
andpost_status
isnew
- or
post_status
ispending_review
andpost_type
is your post type.
Notice the second condition doesn’t specify a meta key, so you’ll get meta_values
for all posts that are pending_review
.
You need to format your conditions so that you’re querying:
meta_key
isselect_analyst
post_type
is your post typepost_status
isnew
orpending_review
The SQL for that would be
WHERE
pm.meta_key = 'select_analyst' AND
p.post_type="post_type" AND
p.post_status IN ('new', 'pending_review' )
So your full query should be:
$query = $wpdb->get_results(
"SELECT
DISTINCT pm.meta_value
FROM
$wpdb->postmeta pm
LEFT JOIN
$wpdb->posts p ON p.ID = pm.post_id
WHERE
pm.meta_key = 'select_analyst' AND
p.post_type = $post_type AND
p.post_status IN ('new', 'pending_review')
ORDER BY
pm.meta_value"
);
A couple of other changes I made:
- You don’t need to prepare the statement. There’s no unsafe user input being put into the query. The only reason you’d want to do that is to save writing the key out multiple times, but you can always just put the variable directly in the statement if it’s not from unsafe input. You weren’t even using any of the variables multiple times (except the meta key, see below) so this was unncessary for your query.
- You were ordering by the literal value
'select_analyst'
, which isn’t going to do anything. I assume you wanted to sort by the meta value, so I did that.
Also note how indenting the code to reflect the structure of the query makes it much easier to spot these sorts of issues.