0

I would like to display posts that have a certain value in a meta field first. I would like to display posts that have a certain value in a meta field first. I have tried it with meta_query and orderby. Unfortunately, only the posts that have exactly this value are displayed, not the others.

$args = array(
    'post_type'      => 'post',
    'posts_per_page' => 10,
    'paged'          => $paged,
    'meta_query'     => array(
        'customid' => array(
            'key' => 'custom_id',
            'compare' => 'LIKE',
            'value' => 12345
        ),
        'customidexists' => array(
            'key' => 'custom_id',
            'compare' => 'EXISTS'
        )
    ),
    'orderby' => array(
        'customid' => 'ASC',
        'customidexists' => 'ASC',
    ),
);

Posts should be output in the following order:

[
    {
        "title": "test1",
        "custom_id": 12345
    },
    {
        "title": "test2",
        "custom_id": 12345
    },
    {
        "title": "test1",
        "custom_id": 45236
    },
    {
        "title": "test1",
        "custom_id": 65442
    }
]

1 Answer 1

1

If I understand it correctly, you would need to use the CASE operator in MySQL/MariaDB like so, to achieve that kind of sorting: (try a live demo on DB Fiddle)

ORDER BY (CASE
    # put posts with the specific meta value to the top
    WHEN (wp_postmeta.meta_value LIKE '%12345%') THEN 1

    # for the rest of posts, sort them by the meta value
    # use wp_postmeta.meta_value+0 if the value is number
    ELSE wp_postmeta.meta_value
END) ASC;

And WordPress or the WP_Meta_Query class doesn't currently support such sorting, but you can use a custom query argument such as orderby_case and then use the posts_orderby filter to sort the posts:

  1. Replace both the orderby and meta_query in your $args with this:

    'meta_query'     => array(
        // NOTE: The array key is necessary! Because we'll use that to get the
        //       alias that was used for this meta query clause.
        'customidexists' => array( // yes, you'd only need this one clause
            'key'     => 'custom_id',
            'compare' => 'EXISTS',
        )
    ),
    'orderby_case'   => 'customidexists', // must match the array key above
    
  2. Add the CASE statement to the ORDER BY clause, using the posts_orderby filter:

    add_filter( 'posts_orderby', 'my_posts_orderby', 10, 2 );
    function my_posts_orderby( $orderby, $query ) {
        $clause_key = $query->get( 'orderby_case' );
        if ( 'customidexists' === $clause_key ) {
            $clauses = $query->meta_query->get_clauses();
    
            if ( isset( $clauses[ $clause_key ] ) ) {
                $alias = $clauses[ $clause_key ]['alias'];
                return "
                    (CASE
                        WHEN ($alias.meta_value LIKE '%12345%') THEN 1
                        # use $alias.meta_value+0 if the value is number
                        ELSE $alias.meta_value
                    END) ASC";
            }
        }
    
        return $orderby;
    }
    
1
  • 1
    This is what i needed. Thank you!
    – Jonas
    Aug 7 at 15:39

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.