Use another field value as condition in Entity Query

Question

I'm performing in Drupal 8 an entity query that searches for nodes that have not been changed since x days, where x is a numeric node field (node.field_days). So I would like to perform a query like:

$query = \Drupal::entityQuery("node")
  ->condition('changed', strtotime('-node.field_days days'), '<=');

I know that condition takes as value a string and not a db column, but is it possible to use entity query with a dynamic value?

Answer 1

The entity query is a powerful and convenient tool to query for content and config entities that have certain properties/field values/field references.

Yet, it has its limitations. Comparing two fields is not possible using the generic entity query and its condition method.

For such a task, you may need to use the more low level dynamic (select) queries and its where method. An example for your use case:

$connection = \Drupal::database();
// Query node base field table.
$query = $connection->select('node_field_data', 'n');
// Join with the `field_days` field table.
$query->leftjoin('node__field_days', 'd', 'd.entity_id = n.nid');
// Query for the node ID.
$query->fields('n', ['nid']);
// Your condition (a day has 86400 seconds; we multiply them with
// the value of the field_days field, add them to the changed
// timestamp of the node and compare it against the current time).
$query->where('n.changed + (d.field_days_value * 86400) <= :now', [
  ':now' => \Drupal::time()->getCurrentTime(),
]);
// Optional sorting.
$query->orderBy('n.changed', 'DESC');
// Fetch results.
$result = $query->execute()->fetchAllKeyed(0, 0);

Above example assumes:

1. that you used a numeric field type for field_days, so its database stored value can be multiplied with seconds without any casting,
2. that the field_days field allows one value only, so we can safely ignore field deltas when joining,
3. that field_days is present on all nodes/bundles and has a valid value,
4. that you use one language only in your site/the field_days value is not translatable, so we can also ignore langcodes.

You may have to alter the query accordingly, if any of these assumptions is not true.