Sort on different date fields if one of it is empty(Views)
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');
}
}