WordPress order posts by 2 custom fields at once

Question

I want to order the posts by 2 custom fields in the same call. How is this possible?

This next code order successfully, but only by only 1 NUMBER custom field (not STRING):

add_action('pre_get_posts', function ($q) {
    if (
        !is_admin() // Target only front end queries
        && $q->is_main_query() // Target the main query only
        && ($q->is_search() || $q->is_post_type_archive('data-base'))
    ) {
        $q->set('meta_key', 'custom_field_1');
        $q->set('order',    'DESC');
        $q->set('orderby',  'meta_value');
    }
});

Update 1:

Currently @Mohammed Yassine CHABLI's first answer works, but it doesn't sort by number, but by String. That means that "81" will come before "9", which is not good. Any solution for that?

Resources that might help:

a more powerful order by in WordPress 4.0

Add meta_type in custom sorting using woocommerce

score 4 · Answer 1 · answered May 14 '20 at 04:32

4

You need the WP way of providing an expression in the ORDER BY;

ORDER BY 0 + meta_key

That is, do something to meta_key to convert it into a numeric value.

answered May 14 '20 at 04:32

Rick James

135,179
13
127
222

Actually this makes a lot of sense, looking at the code: `meta_value_num` is basically `meta_value+0`: `case 'meta_value_num': $orderby_clause = "{$primary_meta_query['alias']}.meta_value+0"; ` – Pablo Martinez May 18 '20 at 14:26

Yassine CHABLI · Answer 2 · 2020-05-12T06:24:44.163

1

Try this one :

configure the meta query :

$meta_query = array(
    'relation' => 'AND',
    'clause1' => array(
        'key'     => 'first key of your meta',
        'compare' => 'EXISTS',
    ),
    'clause2' => array(
        'key'     => 'second key of your meta',
        'compare' => 'EXISTS',
    ));

$q->set('meta_query', $meta_query);


$q->set('orderby',array( 
   'clause1'  => 'DESC', 
   'clause2'  => 'ASC' 
      )  
);

In case you want to sort in the same direction :

$q->set('orderby' =>'clause1 clause2',
        'order'   =>'ASC'
);

edited May 12 '20 at 06:24

answered May 10 '20 at 08:05

Yassine CHABLI

3,459
2
23
43

Thanks, Unfortunately, the first method didn't work, and the second method gives me an error. Any other idea? – Elron May 12 '20 at 03:48
what's the error you get? can you show what have you tried? – Yassine CHABLI May 12 '20 at 05:02
I tried this and it didn't re-order: ` $q->set( 'orderby', array( 'first_order' => 'ASC', 'second_order' => 'ASC', 'third_order' => 'ASC' ) ); ` – Elron May 12 '20 at 05:51
I think this is because it needs to be in the meta_query too, so it can know what "first_order" is. But I didn't manage to make it work – Elron May 12 '20 at 05:52
Your first answer works, but it doesn't sort by numbers, but by String. That means that "81" will come before "9", which is not good. How can we implement this answer to your answer? https://wordpress.stackexchange.com/questions/38657/order-by-numeric-value-for-meta-value – Elron May 13 '20 at 21:00
Just a wild flyer: What happens if you replace `ASC` with `meta_value_num`? – Jerry May 14 '20 at 00:12
Another wild thouht, what if you add `'type' => 'NUMERIC',` to the meta_query clause that defines the numeric field? for example : `'clause2' => array( 'key' => 'second key of your meta', 'compare' => 'EXISTS', 'type' => 'NUMERIC' )` – Pablo Martinez May 18 '20 at 13:28

score 0 · Answer 3 · answered May 18 '20 at 15:18

0

A possible simple solution would be to format the text as if it were a number.

ORDER BY LPAD(meta_key, 5, '0')

Samples:

 9 = 00009
 81 = 00081
 777 = 00777

The weight on the performance of the query is to be evaluated.

answered May 18 '20 at 15:18

Marco Zordan

1
3

WordPress order posts by 2 custom fields at once

3 Answers3

Linked