Sort on different date fields if one of it is empty(Views)

Last updated on 13/01/2020
use \Drupal\views\ViewExecutable;
use \Drupal\views\Plugin\views\query\QueryPluginBase;
use \Drupal\views\Plugin\views\query\Sql;
 * Implements hook_views_query_alter().
 * Alter the views query so we order the Nodes of different types by different
 * date fields.
 * @param \Drupal\views\ViewExecutable $view
 *   The view object about to be processed.
 * @param QueryPluginBase $query
 *   The query plugin object for the query.
function hook_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($query instanceof Sql && $view->id() === 'MY_VIEW_ID') {
    $orderBySql = <<<ORDER_BY_SQL
  WHEN node__field_date.field_date_value IS NULL THEN node_field_data.created
  ELSE UNIX_TIMESTAMP(DATE_FORMAT(node__field_date.field_date_value, '%Y%m%d'))
    // Override existing Order rules.
    $query->orderby = [];
    $query->addOrderBy(NULL, $orderBySql, 'ASC', 'diff_date_fields');