Combine meta query and give a specific meta query a higher priority

Question

there is this loop which sorts by the date. But I want to throw in a specific meta key for a even higher priority. The meta_key "high_prio" should move it to the top of the sorting. (I hope that makes sence).

Let's speak some code:

$loop = new WP_Query( array(
      'post_type' => 'event',
      'posts_per_page' => 3,
      'cat' => '4',
      'orderby' => array( 'meta_start' => 'ASC','meta_prio' => 'DESC'),
      'meta_query' => array(
        'relation' => 'OR',
        'meta_start' => array(
          'key' => 'date_start',
          'type' => 'numeric'
        ),
        'meta_prio' => array(
          'key' => 'high_prio',
          'type' => 'numeric'
        )
      )
    ));

The SQL looks like this now:

SELECT SQL_CALC_FOUND_ROWS pdh50NA5_posts.ID
FROM pdh50NA5_posts
LEFT JOIN pdh50NA5_term_relationships
ON (pdh50NA5_posts.ID = pdh50NA5_term_relationships.object_id)
INNER JOIN pdh50NA5_postmeta
ON ( pdh50NA5_posts.ID = pdh50NA5_postmeta.post_id )
WHERE 1=1
AND ( pdh50NA5_term_relationships.term_taxonomy_id IN (4) )
AND ( pdh50NA5_postmeta.meta_key = 'date_start'
OR pdh50NA5_postmeta.meta_key = 'high_prio' )
AND pdh50NA5_posts.post_type = 'event'
AND (pdh50NA5_posts.post_status = 'publish'
OR pdh50NA5_posts.post_status = 'acf-disabled'
OR pdh50NA5_posts.post_status = 'private')
GROUP BY pdh50NA5_posts.ID
ORDER BY CAST(pdh50NA5_postmeta.meta_value AS SIGNED) ASC, CAST(pdh50NA5_postmeta.meta_value AS SIGNED) DESC
LIMIT 0, 3

I am not good at SQL. I understand the basics but the order part is too much.

Maybe this has to be done differently. Thanks in advance!

Answer 1

As I already pointed in the comment, all you need to do is change your orderby parameter so that the meta_prio is the first item and followed by the meta_start. So your orderby should look like:

// This array forms an ORDER BY clause with each array item being a column that
// MySQL use when sorting the posts/results.
'orderby' => array(
    // This is the first column, which has the highest priority and sorts by the
    // meta high_prio.
    'meta_prio'  => 'DESC',

    // This is the second column, which sorts by the meta date_start, after MySQL
    // sort the results by the meta high_prio.
    'meta_start' => 'ASC',
)

So with the above example, the generated ORDER BY clause would look something like ORDER BY <meta_prio column> DESC, <meta_start column> ASC and MySQL would first sort the results by the high_prio meta and then sort the sorted results by the date_start meta.

And remember that the array key used in your orderby must match the one in your meta_query, if you're sorting the results by the meta query clauses in the meta_query array. Additionally, make sure you set the proper type in your meta query clauses; e.g. if the value is a date like 2020-03-12 09:30, then you'd set the type to DATETIME (i.e. 'type' => 'DATETIME'):

'meta_query' => array(
    'relation'   => 'OR',
    'meta_start' => array(
        'key'  => 'date_start',
        'type' => 'DATETIME',
    ),
    'meta_prio'  => array(
        'key'  => 'high_prio',
        'type' => 'NUMERIC',
    )
),
// Make sure the array keys match those in the above meta_query array.
'orderby'    => array(
    'meta_prio'  => 'DESC',
    'meta_start' => 'ASC',
),

Answer 2

The issue has been the relation. Of course, it must be "AND"

$loop = new WP_Query( array(
      'post_type' => 'event',
      'posts_per_page' => 3,
      'cat' => '4',
      'orderby' => array('meta_prio' => 'DESC', 'meta_start' => 'ASC'),
      'meta_query' => array(
        'relation' => '**AND**',
        'meta_start' => array(
          'key' => 'date_start',
          'type' => 'numeric'
        ),
        'meta_prio' => array(
          'key' => 'high_prio',
          'type' => 'numeric'
        )
      )
    ));