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!

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!

• post-meta
• meta-query

• Have you tried switching the orderby items: 'orderby' => array('meta_prio' => 'DESC', 'meta_start' => 'ASC), ? – Sally CJ Commented Mar 11, 2020 at 6:49
• You gave me an idea to check if the orderby even works after the many updates I did. I just posted an update. – Marv Commented Mar 11, 2020 at 12:58
• And have you tried my suggestion? Did it work? If not, what's the output of echo $loop->request;? – Sally CJ Commented Mar 11, 2020 at 15:24
• Hi @SallyCJ, I've updated the code and added the SQL. – Marv Commented Mar 11, 2020 at 19:09
• And I've posted an answer. I hope it helps. Let me know. :) – Sally CJ Commented Mar 12, 2020 at 9:45

2 Answers 2

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',
),

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