Sort on different date fields in the same Views

Last updated on 13/11/2018
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
CASE
  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'))
ORDER_BY_SQL
SQL;
 
    // Override existing Order rules.
    $query->orderby = [];
    $query->addOrderBy(NULL, $orderBySql, 'ASC', 'diff_date_fields');
  }
}