I'm trying to sort a custom post type by 3 properties:
- Meta Key:
is_featured - Meta Key:
is_primary - Title
The two keys are boolean ACF items, and are both optional, so most of the posts don't have the value at all. I'm trying to get a query that will list all the custom posts, sort them by is_featured first, then is_primary, and finally, by title. This is the query I've got:
return array(
'post_type' => 'cpt',
'post_status' => 'publish',
'posts_per_page' => 6,
'tax_query' => $tax_query,
'meta_query' => array(
'relation' => 'OR',
'is_featured' => array(
'key' => 'cpt_is_featured'
),
'is_primary' => array(
'key' => 'cpt_is_primary'
)
),
'orderby' => array(
'is_featured'=> 'DESC',
'is_primary' => 'DESC',
'title' => 'ASC'
)
);
The original query was simpler, using a meta_key value of is_primary and an orderby array of meta_value => "DESC" and title => "ASC". That produced the desired result with just one meta key. Its SQL looked like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (891)
) AND (
wp_postmeta.meta_key = 'cpt_is_primary'
)
AND wp_posts.post_type = 'cpt'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY
wp_postmeta.meta_value DESC,
wp_posts.post_title ASC LIMIT 0, 6
My client wants to add the second key, though. When I run the query with the meta_query clause above, this is the SQL I get:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND (
wp_term_relationships.term_taxonomy_id IN (891)
) AND (
wp_postmeta.meta_key = 'cpt_is_featured'
OR
wp_postmeta.meta_key = 'cpt_is_primary'
) AND wp_posts.post_type = 'cpt'
AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID
ORDER BY
CAST(wp_postmeta.meta_value AS CHAR) DESC,
CAST(wp_postmeta.meta_value AS CHAR) DESC,
wp_posts.post_title ASC
LIMIT 0, 6
This results in the same data returned as the first SQL query, but the order of the two meta_values is irrelevant, since the ORDER BY clause contains no reference to the individual meta_keys.
I can see how meta_query would be useful for narrowing down a query, but for sorting, I don't see how it can be used for multiple values?