Display posts with specific value first in query

Question

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
    }
]

Answer 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;
   }