4

Assume we have an entity query like the following:

$query = \Drupal::service('entity_type.manager')->getStorage('taxonomy_term')->getQuery();
$tids = $query->condition('vid', 'tags')->execute();

and we need to add some SQL expression and condition based on it, like the following:

Replace(name, ' ', '-') = 'some-tag'

How to do that?

The Drupal\Core\Entity\Query\Sql\Query class does not have methods for doing so (unlike Drupal\Core\Database\Query\Select).

1 Answer 1

6

Using query tags and metadata the underlying Select query can be altered easily.

$query = \Drupal::service('entity_type.manager')->getStorage('taxonomy_term')->getQuery();

// Add tag and any metadata to the entity query.
$query->addTag('my_category')
  ->addMetaData('my_category', $dynamic_category_name);

$tids = $query->condition('vid', 'tags')->execute();

And then in my_module.module file:

/**
 * Implements hook_query_TAG_alter() for my_category tag.
 */
function my_module_query_my_category_alter(\Drupal\Core\Database\Query\AlterableInterface $query) {
  $expression = "Replace(taxonomy_term_field_data.name, ' ', '-')";
  $args = [
    ':dynamic_category_name' => $query->getMetaData('my_category'),
  ];

  $query->addExpression($expression);
  $query->where($expression . ' = :dynamic_category_name', $args);
}
2
  • This is a really nice solution example. I want to try something similar, but with ->condition('BINARY field_on_my_taxonomy_term', $values, 'IN') to do a binary-collated comparison. And while I did find a way to get it working, I had to do some odd things, since it's a field in a different table. I had to add a condition like ->condition('field_on_my_taxonomy_term', 'ALWAYS_TRUE', '<>'), so that the needed JOINs are added. continued in next comment...
    – mbomb007
    Sep 15, 2023 at 19:15
  • Example of what I had to use: ->addTag('where')->condition('field_on_my_taxonomy_term', '( always_true )', '<>')->addMetaData('where', ['snippet' => 'BINARY field_on_my_taxonomy_term_value IN (:array[])', 'args' => [':array[]' => $values]]). I'm not sure if there's a better way.
    – mbomb007
    Sep 15, 2023 at 19:16

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.