3

I want to sort some entities in ascending expiration date, but I want those without expiration date to be at the end. Is there a way to add a CASE expression like this to the Drupal::entityQuery?

    $entity_query = \Drupal::entityQuery($entity_type)
     ->accessCheck(FALSE)
     ->condition($some_condition)
     ->addExpression("CASE WHEN $table.$sorting_field IS NULL THEN 1 ELSE 0 END")
     ->sort($values)
1
  • No, not with Drupal::entityQuery. addExpression adds itself to the field list, e.g. something like select <expression> from table. You can not use addExpression to manipulate the from, where or order by clause. You have to go deeper and manually create a database query for advanced SQL gimmicks.
    – Hudri
    Sep 27, 2023 at 16:46

1 Answer 1

2

You can add a tag to the entity query, and then use hook_query_TAG_alter() to manipulate it when it's been converted to a standard SQL query. e.g.

$entity_query = \Drupal::entityQuery($entity_type)
  ->addTag('advanced_sort')
  ->accessCheck(FALSE)
  ->condition($some_condition)
  ->sort($values);

...

function my_module_query_advanced_sort_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  // Add a field for the sort value.
  $alias = $query->addExpression(
    "CASE WHEN $table.$sorting_field IS NULL THEN 1 ELSE 0 END", 
    'foo_alias', 
    []
  );
  
  // Sort by it.
  $query->orderBy($alias);
}

The docs linked above have some examples on how to determine the alias for $table, you'll probably need to inspect the query a bit further to get a reliable value for $sorting_field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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