Is it possible to have a nested SELECT in a WHERE clause using dynamic queries?

Question

I need to execute this SQL query using dynamic queries.

WHERE (node_field_data.status = 1) 
AND (node__field_document_places.entity_id IN (SELECT entity_id FROM node__field_document_places where field_document_places_target_id = 51673)) 

This is the code I am using.

$connection = Database::getConnection();
$query = $connection->select('node_field_data', 'node_field_data');

$query->condition('node_field_data.status',1);
$query->condition('node__field_document_places.entity_id',[ -- Can I do this here somehow? -- ], "IN");

I could technically run a separate query and put the IDs in an array. But I was hoping to avoid executing two different queries.

Is there a way to do this using dynamic queries?

Answer 1

Yes, this is very possible, see the Subqueries can now be added to query conditions change record for full details.

The canonical example is very similar to yours and should demonstrate how to refactor your code:

// Create a subquery, which is just a normal query object.
$subquery = db_select('test', 't2');
$subquery->addExpression('AVG(t2.age)');

// Create another query that adds a clause using the subquery.
$select = db_select('test', 't');
$select->addField('t', 'name');
$select->condition('t.age', $subquery, '<');

Don't forget to replace the deprecated db_select() with $connection->select().