How can I add a condition to a Drupal 9 entity query using another entity query on a different entity type?
I'll illustrate the question by describing one specific use case, though I don't think it would be difficult to come up with any number of plausible use cases.
The application for this use case tracks articles in published medical literature. An article is represented by a custom entity type which tracks when it was imported into the system, whether the full text for the article has been obtained, which topics have been assigned to the article for which review cycles, when and in which journal was it published, and lots of other information.
A second custom entity type exists to represent batches ("packets") of articles assigned for review, tracking when the batches were assigned, for which topics, and to which oncology specialists, as well as what the results of those reviews were. An article can be assigned to many review packets, each with multiple assigned reviewers, and each review packet can request reviews for any number of articles.
The search module for this application has a requirement that articles can be searched for based on many different criteria, most of which can be applied by inspecting the Article entities, but some of which can only be implemented by looking at values in the Packet entities. An example search might, for example, request to find all articles published within the past twelve months in journal J, and assigned for review by reviewer R.
In a CMS more suited to working directly with the relational SQL tables it would be very straightforward to support such a search using a couple of table joins. But Drupal makes that approach less appealing, as we have no documented guarantee that the table and column naming conventions underneath entities can be relied on not to change.
I could create and execute two separate entity queries and use the results of the second to weed out results returned by the first query which are not also present in the results of the second. But among the drawbacks of this approach is the fact that it breaks the Drupal paging mechanism.
I could try to mimic what the DBMS would be doing much more efficiently by feeding the entity IDs returned by the second query into a condition on the first query, but who knows what internal limits that might run into when the results of the second query are huge?
I could implement a kludge to denormalize the data, storing entity references from the Article entities back to the Packet entities which are in turn pointing back to the Article entities, but we all know the problems with trying to keep the same information in multiple places in sync.
If all the searches were for articles assigned for review, I could turn things around and use an entity query on the Packet type, following its reference to Article entities for the other criteria which need to be tested. But a large percentage of the searches are meant to find articles whether or not they have been assigned for review. So for this path I'd have to implement two separate sets of search logic in parallel, one for searches which involve assignment to review packets and a second for searches which don't care about assignment for review. And this approach still wouldn't address searches looking explicitly for articles which have not been assigned for review. And bear in mind, I'm keeping this use case example simple by restricting it to only two entity types.
Googling (and searching this forum) keeps diverting me to information on using the underling database query APIs instead of the entity query system, at least with the query terms I'm using.
Is there a way to use a subquery (or even better, join to another entity type) in the entity query APIs? If not, are there prospects for supporting such functionality under discussion in Drupal Valhalla?
