Meta query field order together with post_date order causes posts without the meta field to be unordered

I’ve managed to get wordpress to do a descending order on a meta_query field called start_date which is saved in Ymd format in the database by the ACF plugin.

However in my query the other post types that doesn’t have this field are unordered and also seems to always come after all the posts which contain the meta field.

Example on how the order currently is returned:

This is an extract on the pre_get_posts hook i’m using.

function orderby_fieldifexists($orderby) {
    return "mt1.post_id IS NOT NULL DESC, wp_postmeta.meta_value DESC, wp_posts.post_date DESC";
}
add_filter("posts_orderby", "orderby_fieldifexists", 10, 1);

$meta_query = [
    'relation' => 'OR',
    'start_date_value' => [
        'key' => 'start_date',
    ],
    'start_date' => [
        'key' => 'start_date',
        'compare' => 'NOT EXISTS'
    ]
];

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

Does anyone have any clue on why this might be happening? I’ve been stuck with this issue for way too long time now and I’m asking here if anyone else might spot what I’m doing wrong.

Here’s also the SQL output from the query (that contains a few chunks of translation joins from wpml):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
         LEFT JOIN wp_postmeta
                   ON ( wp_posts.ID = wp_postmeta.post_id )
         LEFT JOIN wp_postmeta AS mt1
                   ON (wp_posts.ID = mt1.post_id
                       AND mt1.meta_key = 'start_date' )
         LEFT JOIN wp_icl_translations wpml_translations
                   ON wp_posts.ID = wpml_translations.element_id
                       AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)
WHERE 1=1
  AND (((wp_posts.post_title LIKE '%Test%')
    OR (wp_posts.post_excerpt LIKE '%Test%')
    OR (wp_posts.post_content LIKE '%Test%')))
  AND ( wp_postmeta.meta_key = 'start_date'
    OR mt1.post_id IS NULL )
  AND wp_posts.post_type IN ('post', 'page', 'news', 'publication', 'event')
  AND (wp_posts.post_status="publish"
    OR wp_posts.post_status="acf-disabled"
    OR wp_posts.post_author = 1
           AND wp_posts.post_status="private")
  AND ( ( ( wpml_translations.language_code="en"
    OR ( wpml_translations.language_code="en"
        AND wp_posts.post_type IN ( 'project' )
        AND ( ( (
                    SELECT COUNT(element_id)
                    FROM wp_icl_translations
                    WHERE trid = wpml_translations.trid
                      AND language_code="en" ) = 0 )
            OR ( (
                     SELECT COUNT(element_id)
                     FROM wp_icl_translations t2 JOIN wp_posts p
                                                      ON p.id = t2.element_id
                     WHERE t2.trid = wpml_translations.trid
                       AND t2.language_code="en"
                       AND ( p.post_status="publish"
                         OR p.post_type="attachment"
                                 AND p.post_status="inherit" ) ) = 0 ) ) ) )
    AND wp_posts.post_type IN ('post','page','attachment','wp_block','news','publication','event','project' ) )
    OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','news','publication','event','project') )
GROUP BY wp_posts.ID
ORDER BY mt1.post_id IS NOT NULL DESC, wp_postmeta.meta_value DESC, wp_posts.post_date DESC
LIMIT 0, 10

Actually, I don’t think you need that mt1.post_id IS NOT NULL DESC sorting, hence you also do not need to use the posts_orderby hook. You can just use the orderby parameter in WP_Query like so:

Also note that, in the first meta query clause named/keyed start_date_value, you should set the type arg to DATE (like you can see below) so that MySQL/MariaDB casts the meta value as a date and then properly sorts the posts by date.

$meta_query = [
    'relation'         => 'OR',
    'start_date_value' => [
        'key'  => 'start_date',
        'type' => 'DATE', // set the 'type' to DATE
    ],
    'start_date'       => [
        'key'     => 'start_date',
        'compare' => 'NOT EXISTS',
    ],
];

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

// Use an array with the orderby.
$query->set( 'orderby', [
    'start_date_value' => 'DESC', // sorts by the meta start_date
    'post_date'        => 'DESC', // then sorts by the post date
] );

And as I said in the comments, you should understand that when sorting by multiple columns like above where the columns are the start_date meta and the post date, values that are already sorted in the previous column won’t be sorted again, unless for values that are equal like the 20200211 (start_date) below. You can try a demo on DB Fiddle which outputs this:

---
**Query #1 - before sorting **

| Post Title    | start_date | Post Date              |
| ------------- | ---------- | ---------------------- |
| News 1        | 20200211   | March 12th, 4:00AM     |
| News 2        | 20200926   | September 19th, 5:36AM |
| News 3        |            | June 5th, 8:07PM       |
| Publication 1 | 20201021   | October 16th, 5:40PM   |
| Publication 2 | 20200211   | March 12th, 11:43AM    |

---
**Query #2 - after sorting by the meta start_date **

| Post Title    | start_date | Post Date              |
| ------------- | ---------- | ---------------------- |
| Publication 1 | 20201021   | October 16th, 5:40PM   |
| News 2        | 20200926   | September 19th, 5:36AM |
| News 1        | 20200211   | March 12th, 4:00AM     |
| Publication 2 | 20200211   | March 12th, 11:43AM    |
| News 3        |            | June 5th, 8:07PM       |

---
**Query #3 - after sorting by the meta start_date, AND THEN the post date **

| Post Title    | start_date | Post Date              |
| ------------- | ---------- | ---------------------- |
| Publication 1 | 20201021   | October 16th, 5:40PM   |
| News 2        | 20200926   | September 19th, 5:36AM |
| Publication 2 | 20200211   | March 12th, 11:43AM    |
| News 1        | 20200211   | March 12th, 4:00AM     |
| News 3        |            | June 5th, 8:07PM       |

Note though, the demo doesn’t use the full WordPress database schema, but I just wanted to demonstrate the multiple columns sorting. And I hope that demo and this answer helps you? 🙂