How to sort on different columns in the same view?

Question

I'm trying to build a Glossary View in Drupal 8. Let's say I have 3 content types: Person, Article, Event. I want sort by title ASC for Article and Event, but sort by Last Name for Person. So that listing would show something like.

Deep Study on Dolphins (article) John Doe (person) Jam in the Backyard (event)

And Not

Deep Study Jam in the Backyard John Doe

I think I can do this with hook_views_query_alter, but the only examples I've seen are for Drupal 7.

I've got something like this

function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'glossary_everything') {
     $query->orderby[0]['field'] = "CASE WHEN node_field_data.last_name IS NULL THEN node_field_data.title ELSE node_field_data.last_name END";
  }
}

But I'm getting a "column not found" sql error. I think it's treating the whole CASE statement as one big field. Is that because of the ['field'] designation in my array?

Answer 1

In Drupal 8, if you want to alter an ORDER BY, in particular to use a formula, you have to add an expression and then order by it:

$query->addExpression('SUBSTRING(thread, 1, (LENGTH(thread) - 1))', 'order_field');
$query->orderBy('order_field', 'ASC');

There can be some confusion in Views, because their version of addExpression is Sql::addField. And in fact, Sql::addOrderBy let's you add a field and order by it in one call.

So, your code would look like:

$sql = <<<SQL
CASE
     WHEN node_field_data.last_name IS NULL THEN node_field_data.title
          ELSE node_field_data.last_name
END
SQL;

$query->addField(NULL, $sql, 'last_name_else_title');
$query->orderby[] = array(
  'field' => 'last_name_else_title',
  'direction' => 'ASC',
);

Or just...

$query->addOrderBy(NULL, $sql, 'ASC', 'last_name_else_title');

Thanks agileadam for pointing out the failure point (see also).

Answer 2

This is caused by the call to $this->connection->escapeField($field) in \Drupal\Core\Database\Query\Select::__toString().

\Drupal\Core\Database\Connection::escapeField is wiping out the spaces (CASE WHEN becomes CASEWHEN, for example), which renders your statement invalid.

I don't have time to come up with the solution, but that's where it's breaking.

Answer 3

From this example, this is how you would do to sort by a date field and Node published date:

   $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'))
END
ORDER_BY_SQL;

    // Override existing Order rules.
    $query->orderby = [];
    $query->addOrderBy(NULL, $orderBySql, 'ASC', 'diff_date_fields');