0

I'm trying to sort a custom post type by 3 properties:

  1. Meta Key: is_featured
  2. Meta Key: is_primary
  3. 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?

  • I think the issue is that it's an OR relationship, you can't order by something that doesn't exist for some of the posts. – Milo Nov 16 '18 at 4:01
  • That makes sense. The issue is that setting it to AND eliminates all the posts that don't have that meta field set, so it acts as a filter. I think I'm going to need to sort it after the query returns, but was hoping to do it in one go. – ken.dunnington Nov 16 '18 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.