I have several entities (content types) using node references to relate the content together.
I need to write queries which relate two or more nodes together through their node reference fields, then apply conditions based on fields from each of the entities and returning at the very least the nids that meet those conditions.
An example: Engagement attaches to Event via node reference. I need to query for Engagement entities within a certain date range. The date field is defined in the Event CT. So what I want is to link the Engagement 'entity' with the Event 'entity', apply the date range condition to the Event entity, and return the nids of the Engagement entities meeting the condition.
This is one somewhat simple example, and there are other relationships which will require similar queries...
So far it seems I may need to extend the EntityFieldQuery class, but I'm not sure that this will get me what I want.
This effort is going towards converting a site I built in D6 into D7. I wrote a custom query builder to accomplish these queries by just manually building the queries. I'm attempting to convert these queries to use D7's query building architecture.
It may also be possible to write these queries using db_select and manually linking in the necessary tables (basically what I did for D6).
I'm at a bit of a crossroads. I'm going to need to make this happen. Any help at this point would be quite helpful. Does anyone have any experience with this type of problem or extending the EntityFieldQuery class?