How can I use order_by to order by two meta_keys without excluding posts that don't have those keys initialized?

Question

I have two meta_keys on a custom post type. I want to be able to query all of these posts, and order them by the two meta_key, one taking precedence over the other.

I.e. I have one meta_key called stickied, these should always appear first. The second meta_key is popularity, which is a basic hit count for that post.

When I use meta_query, it seems that posts without the meta keys initialized will not appear in the result set. I want all posts regardless of whether they have the meta_key initialized or not, and then order them based on those meta_key.

Is this possible?

Answer 1

I had a similar issue but couldn't solved with the snippets on this thread.

I had to order a query by:

1. all 'featured' posts first (is_it_a_featured_etf) and
2. by a numeric field (etf_aum) in DESC order after the featured ones.

My solution:

'meta_query'   => [
  'relation'    => 'OR',

  'etf_aum'    => array(
    'key'     => 'etf_aum',
    'type'    => 'NUMERIC',
    'compare' => 'EXISTS',
  ),

  'is_it_a_featured_etf' => array(
    'key'       => 'is_it_a_featured_etf',
    'compare'   => 'EXISTS',
    'value'     => '1',
    'operator'  => '=',
  ),
],
'orderby' => [
  'is_it_a_featured_etf' => 'ASC',
  'etf_aum' => 'DESC',
]

Answer 2

As I understand, you are trying to sort the post by meta values. In such cases, you can use 'orderby => 'meta_value'. So your query will look like this:

$args = array(
    'orderby'   => 'meta_value',
    'meta_key'  => 'stickied',
);
$query = new WP_Query( $args );

Not that orderby can accept multiple values, such as:

$args = array(
    'post_type' => 'page',
    'orderby'   => array( 'title' => 'DESC', 'meta_value' => 'ASC' ),
    'meta_key'  => 'stickied',

);

However I'm not sure if you can use multiple meta values, but it is probably possible. At the moment I don't have such condition in my database to test it, but based on my example and this codex page you should be able to easily try it out.

Please let me know if sorting by multiple values worked for you based on above structure.

UPDATE

After digging for a while, I've found this answer and this code that provides a solution to this situation:

$args = array(
    'post_type' => 'post',
    'orderby' => 'meta_key',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'OR',
        array( 
            'key'=>'stickied',
            'compare' => 'EXISTS'           
        ),
        array( 
            'key'=>'popularity',
            'compare' => 'EXISTS'           
        )
    ),
    'post_per_page'=>-1
);

$query = new WP_Query($args);

Answer 3

There are a number of ways to go about solving something like this. The real problem comes if/when you need to apply default pagination to the query.

The best way to handle this, and preserve the ability to paginate, would be to use a combination of meta_query clauses. Jack Johansson was on the right track, this just requires a bit more logic in the query. You may need to adjust the exact combination a bit to suit your needs, as this is just an estimated guess based on the info provided.

Try something like this:

$args = array(
    'post_type' => 'post',
    'meta_query' => array(
        'relation' => 'OR',
        'sticky_pop_clause' => array(
            'relation' => 'AND',
            'sp_sticky' => array( 
                'key' => 'stickied',
                'compare' => 'EXISTS' 
            ),
            'sp_pop' => array( 
                'key' => 'popularity',
                'compare' => 'EXISTS'           
            )     
        ),
        'sticky_clause' => array( 
            'key' => 'stickied',
            'compare' => 'EXISTS'           
        ),
        'popular_clause' => array( 
            'key'=>'popularity',
            'compare' => 'EXISTS'           
        ),
        'other_clause' => array(
            'relation' => 'AND',
            array( 
                'key'=>'stickied',
                'compare' => 'NOT EXISTS'           
            ),
            array( 
                'key'=>'popularity',
                'compare' => 'NOT EXISTS'           
            )
        )
    ),
    'orderby' => array(
        'sp_pop' => 'DESC',
        'sticky_clause' => 'ASC',
        'popular_clause' => 'DESC',
        'date' => 'DESC'
    )
);

$query = new WP_Query($args);

Answer 4

You would need to check all (select) the posts with and without stickied and popularity with both EXISTS and NOT EXISTS. After that you sort it with orderby.

Didn't test it. Modify it to your needs. I did smth similar. Results were okay.

$args = [
    'meta_query' => [
        'relation' => 'OR',
        'with_stickied' => [
            'key'=>'stickied',
            'compare' => 'EXISTS'           
        ],
        'without_stickied' => [
            'key'=>'stickied',
            'compare' => 'NOT EXISTS'           
        ],
        'with_popularity' => [
            'key'=>'popularity',
            'compare' => 'EXISTS'           
        ],
        'with_popularity' => [
            'key'=>'popularity',
            'compare' => 'NOT EXISTS'           
        ],
    ],
    'orderby' => [
            'with_stickied' => 'DESC',
            'with_popularity' => 'DESC',
    ]
];

You might want to check "‘orderby’ with multiple ‘meta_key’s" from WP Codex.