8

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

2
  • Did you ever find a solution? Currently facing the same issue.
    – Nate
    Jan 3, 2018 at 10:02
  • 2
    I went with the solution posted on my edit. Tagging the query then using my module to customize it. Thats the best approach. If you cant figure it out, I can post an example
    – 113408
    Jan 3, 2018 at 10:06

1 Answer 1

0

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

Your Answer

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

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