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?


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
     WHEN node_field_data.last_name IS NULL THEN node_field_data.title
          ELSE node_field_data.last_name

$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).


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.


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.