Add Expression for Entity Query

Question

I've been working with the entityQuery to query the db and get an array of filtered nids however I need to add an expression to my query.

I've checked the Query class and found a protected sqlQuery attribute that has access to addExpression(). In my case I'm not extending the class but rather instantiating it through \Drupal::entityQuery('node').

Also after checking a bit I found that the Select class has a function addExpression() that does exactly what I need, but then I lose the entityQuery and has to addJoin/addField and so on for each of the fields I would like to include in my conditions.

My objective:

$expression = "
    (
        6371000
        * acos(
            cos( radians({$latitude}) )
            * cos( radians( {$latitudeField} ) )
            * cos(
                radians( {$longitudeField} )
                - radians({$longitude})
            )
            + sin( radians({$latitude}) )
            * sin( radians( {$latitudeField} ) )
        )
    )";
$query = \Drupal::entityQuery('node')
->condition('status', 1)
->condition(`field_city`,1,'=')
->addExpression($expression, 'distance');

$nids = $query->execute();

Did I miss something ? Is there any other way to achieve what I want ?

EDIT: I found this approach but doesn't seem straight forward

EDIT2: Better approach is this

Answer 1

Is there any other way to achieve what I want ?

Nope. Sorry. Entity query is backend agnostic and doesn't support SQL. It wouldn't make much sense using it with config queries for example. You could manually assemble your SQL query against the default SQL entity storage using table mapping:

$storage = \Drupal::entityTypeManager()->getStorage('node');
if (!$storage instanceof SqlEntityStorageInterface) {
  return;
}
$mapping = $storage->getTableMapping();

and from there you can call the methods on TableMappingInterface to find table and column names.

$query = \Drupal::database()->select($storage->getDataTable(), 'n');
// We know status is stored in the data table, no need to make this fully generic.
$query->condition('n.status', 1)
// But we know fields are in dedicated tables.
$city = $mapping->getFieldTableName('field_city`);
$query->innerJoin($city, 'c', 'c.entity_id = n.nid AND c.langcode = n.langcode');
// It's not clear what type field_city is, use whatever property is 
// appropriate here instead of value. 
$column = $mapping->getColumnNames('field_city')['value'];
$query->condition("c.$column", 1);
// Do the same for latitude and longitude tables and column names...
// Finally:
$query->addExpression($expression, 'distance');

It's a balancing act when reaching this deep of what you hardwire and what you do not. For example, we presumed status is not stored in a separate table. In a future Drupal version it might become one. Is it worth the code complexity? Not in my opinion. Your opinion might differ. You need to retrieve the field storage definitions from entity_field.manager and check $mapping->requiresDedicatedTableStorage($definition).

Another thing we hardwired is the dedicated field tables use entity_id and langcode as respective column names for that data. This is much less of an opinion whether to do so: there's no API to query them (getExtraColumns is not keyed so there's not really a way to figure out what those columns mean) so it's reasonable to expect core will keep those column names as they are.

I also hardwired nid and langcode. You could retrieve the entity type definition, run getKey('id') and getKey('langcode') and then use those as field names and retrieve their column names... utterly pointless in my opinion. Even more so than status.

Now, I did not hardwire node__field_city. Unlike the previous paragraphs, expecting table names to be node__fieldname is not valid even at present time: table names for longer than 48 character field names get hashed. Who knows what the future might bring. Also the API is very simple and straightforward quite unlike the previous ones.

Answer 2

I am not sure if following is what you're looking for, it helped me when I was doing something similar

    $sql_snippet = <<<EOF

ROUND(
  CASE $this->tableAlias.$unit_column
    WHEN 'kg' THEN $this->tableAlias.$this->realField * :kg_coefficient
    WHEN 'lb' THEN $this->tableAlias.$this->realField * :lb_coefficient
  END
, :precision) = :value
EOF;

    // The difference in this invocation of ::addWhereExpression() compared to
    // the invocation in the filter handler is that here part of the DB
    // placeholders come from $this->options (such as precision) and others come
    // from the actual argument we have received, i.e. derived from
    // $this->argument.
    $this->query->addWhereExpression(0, $sql_snippet, [
      ':value' => round($arg['value'], $this->options['precision']),
      ':kg_coefficient' => $kg_coefficient,
      ':lb_coefficient' => $lb_coefficient,
      ':precision' => $this->options['precision'],
    ]);
  }

Lifted from Here