wp query - Sorting with meta_query and multiple, optional meta keys

admin管理员组

文章数量:1130349

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'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?

本文标签： wp querySorting with metaquery and multipleoptional meta keys