how to show records that don't have custom meta value

admin管理员组

文章数量:1200790

Running pre_get_posts, I want to order the items by a custom meta value. The problem is that by default this doesn't exist. Only if a user performs a certain action does the meta key/value get added.

That being said, even if they don't ever click on the item itself, I would like it to be displayed. Basically the order is the number of clicks an items has received.

So, the most clicked item should be first and the items that haven't been clicked yet should be last, but they are not showing up at all.

function show_user_counts( $query ) {

  if( !is_admin() && $query->is_main_query() && $query->is_tax('my_category')) {
    $query->set('meta_key', 'number_clicks' );
    $query->set('orderby', 'meta_value'  );
    $query->set( 'order', 'DESC' );
  }
}
add_action('pre_get_posts', 'show_user_counts', 9999);

Running pre_get_posts, I want to order the items by a custom meta value. The problem is that by default this doesn't exist. Only if a user performs a certain action does the meta key/value get added.

That being said, even if they don't ever click on the item itself, I would like it to be displayed. Basically the order is the number of clicks an items has received.

So, the most clicked item should be first and the items that haven't been clicked yet should be last, but they are not showing up at all.

function show_user_counts( $query ) {

  if( !is_admin() && $query->is_main_query() && $query->is_tax('my_category')) {
    $query->set('meta_key', 'number_clicks' );
    $query->set('orderby', 'meta_value'  );
    $query->set( 'order', 'DESC' );
  }
}
add_action('pre_get_posts', 'show_user_counts', 9999);

• custom-post-types
• custom-taxonomy
• post-meta
• pre-get-posts

Share Improve this question asked May 7, 2022 at 13:14 user8463989user8463989 59311 gold badge88 silver badges2424 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

the most clicked item should be first and the items that haven't been clicked yet should be last, but they are not showing up at all

They aren't showing up because your posts query is only selecting posts that have the number_clicks meta, and thus MySQL ignores all posts which do not have that meta.

So what you need is two meta query clauses with an OR relation — the 1st clause will select posts that have the number_clicks meta, whereas the 2nd clause will select posts without that meta.

And for that, you can instead use the meta_query arg, so in your code, just replace the $query->set('meta_key', 'number_clicks' ); with:

$query->set( 'meta_query', array(
    'relation'       => 'OR',

    'number_clicks'  => array( // 1st clause
        'key' => 'number_clicks',
    ),

    'number_clicks2' => array( // 2nd clause
        'key'     => 'number_clicks',
        'compare' => 'NOT EXISTS',
        'type'    => 'NUMERIC',
    ),
) );

Then to sort the results by the number_clicks value, set the orderby to the array key (number_clicks2) for the 2nd clause above, i.e. $query->set( 'orderby', 'number_clicks2' ).

And note that the 2nd clause above uses NUMERIC as the type so that MySQL will convert the meta value to a number which then gives us the correct numerical sorting.

• Remember that meta values are stored as strings/text in the database, hence for example (these individual values — ) 10, 4, 1, 3, 2 will be sorted into 1, 10, 2, 3, 4 instead of 1, 2, 3, 4, 10. Because that's what happen when the values are sorted as strings.

• So actually, you should've used $query->set( 'orderby', 'meta_value_num' ) so that the meta value is converted to a number prior to sorting. But now you no longer need to use that because the meta value type is already set in the 2nd clause above.

本文标签： how to show records that don39t have custom meta value