0

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);

1 Answer 1

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.

1
  • 1
    Fantastic! Thanks Sally! May 10 at 6:04

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.