Most performant way to check if a user is referenced in any node of a specified type?

Question

I need to get a list of all users that have a specific role. Those users can be referenced in a multivalue field of a specific content type; some users are referenced in multiple nodes of that content type, some in none. I want to filter out all users that are not referenced in any node of that content type. Here's my current (functional) code:

 $ids = \Drupal::entityQuery('user')
 ->condition('status', 1)
 ->condition('roles', 'dozent')
 ->sort('field_name', 'ASC')
 ->execute();

 foreach($ids as $k => $id){
   $references = \Drupal::entityQuery('node')
   ->condition('type', 'angebot')
   ->condition('field_dozenten', $id)
   ->count()
   ->execute();
   if(intval($references) === 0) unset($ids[$id]);
 }

This works as intended as far as I can tell, but performant-wise it seems quite inelegant due to the large amount of database queries. Is there a better way? Can I somehow include the condition that a user must be referenced in at least one node in the first entityQuery? If not, is there a more performant way to perform the second query (e.g. a way to only search for the first reference from a node to the user, since I don't need the exact count anyway)? I'm open to other suggestions as well.

Answer 1

I suggest you NOT to worry about performance until it's not a real issue. That doesn't mean of course you can write any silly things you want to. The code snippet you posted is just fine. You are using the Drupal API, which is a good thing to do.

If you really want to speed up things, to make it for example with ONE query (instead of two) you can use:

\Drupal::database()->select("..."); // With a proper dependency injection

About how to write queries in Drupal connection class there is a lot of good tutorials. If you wouldn't find one, please let me know.

However, I only would use this, if it's a REALY performance issue. It's never a good thing "having a knowledge" in your production code about your table schemes. Always start with EntityTypeManager or entityQuery (as you did).