orderby = meta_value_num not working

[lichtmetzger]

I have an event post type with a custom meta (ACF) field "event_date".

This query works fine without the network option:

$event_args = array(
      'post_type' => 'event',
      'posts_per_page' => -1,
      'meta_key' => 'event_date',
      'orderby' => 'meta_value_num',
      'order' => 'ASC'
);

This one returns no results:

$event_args = array(
      'network' => true,
      'post_type' => 'event',
      'posts_per_page' => -1,
      'meta_key' => 'event_date',
      'orderby' => 'meta_value_num',
      'order' => 'ASC'
);

[lichtmetzger]

Now I know why it doesn't work.
In the SQL statement, it's trying to order by the meta_value in the wp_postmeta table.
But: The wp_postmeta values are not available in a network query.

In a "normal" / single site query, it's doing this:

SELECT
  SQL_CALC_FOUND_ROWS wp_2_posts.*
FROM
  wp_2_posts
  INNER JOIN wp_2_postmeta ON (wp_2_posts.ID = wp_2_postmeta.post_id)
WHERE
  ...
ORDER BY
  CAST(wp_2_postmeta.meta_value AS DATETIME) ASC

The ORDER BY works here, because meta_value is accessible.

It does not work with a network query, because the main SELECT statement contains more SELECT statements that are glued together with UNION. meta_value is inaccessible here:

SELECT
  SQL_CALC_FOUND_ROWS tables.*
FROM
  (
    (
      SELECT
        wp_posts.*,
        '1' AS site_ID
      FROM
        wp_posts
        INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
      WHERE
        ...
      GROUP BY
        wp_posts.ID
      ...
    )
    UNION
    (
      SELECT
        wp_2_posts.*,
        '2' AS site_ID
      FROM
        wp_2_posts
        INNER JOIN wp_2_postmeta ON (wp_2_posts.ID = wp_2_postmeta.post_id)
      WHERE
        ...
      GROUP BY
        wp_2_posts.ID
      ...
    )
  ) tables
ORDER BY
  CAST(wp_postmeta.meta_value AS DATETIME) ASC
...

I actually know how to fix this statement, someone just needs to figure out how to put this altered statement into the plugin. This would work and return the correct results. I add my meta_value as qb_meta_column in the inner selects to get the value as a result. Then I can order by the qb_meta_column at the end:

SELECT
  SQL_CALC_FOUND_ROWS tables.*
FROM
  (
    (
      SELECT
        wp_posts.*,
        '1' AS site_ID,
        meta_value AS qb_meta_column
      FROM
        wp_posts
        INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
      WHERE
        ...
      GROUP BY
        wp_posts.ID
      ...
    )
    UNION
    (
      SELECT
        wp_2_posts.*,
        '2' AS site_ID,
        meta_value AS qb_meta_column
      FROM
        wp_2_posts
        INNER JOIN wp_2_postmeta ON (wp_2_posts.ID = wp_2_postmeta.post_id)
      WHERE
        ...
      GROUP BY
        wp_2_posts.ID
      ...
    )
  ) tables
ORDER BY
  CAST(qb_meta_column AS DATETIME) ASC
...