Query Drupal 8 content types with expression

Question

In short, I need to do an entity query while running an expression. I can't find any way to accomplish this. I'm assuming there should be two ways.

1. An entity query with an expression - When I try this I can't get any expressions to work
2. a raw DB query, but I'm not familiar with how to join all the fields related to the content type of nodes I need.

Here is a shorthand example of what I need to accomplish

X = content type
y = field on x content type
z = field on x content type
My expression below is just an example, but need to run this in the database query

- Select y and z from x
- if x > y return node id

Any help would be great. This is going to run on a very large dataset, trying to find the fastest way to do the query in the database.

Answer 1

Comparing two fields is not possible using the entity query.

To do this, you may need to use the more low level Dynamic Queries and its where method:

$query = \Drupal::database()->select('node_field_data', 'n');
$query->condition('n.type', 'x');                               // to get content type x
$query->innerJoin('node__field_y', 'y', 'y.entity_id = n.nid'); // join with field y
$query->innerJoin('node__field_z', 'z', 'z.entity_id = n.nid'); // join with field z
$query->where('z.field_z_value > y.field_y_value');             // your condition
$query->addField('n', 'nid');                                   // get node id in result
$results = $query->execute()->fetchAllKeyed(0, 0);