Linking entities using EntityFieldQuery?

Question

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?

Answer 1

Well your approaches sound ok ... an alternative might be the Relation module. You can define a relation such as "event-has-engagement" between any number of entity ids. A "relation" is just given a numerical id typically and then the endpoints go into a table.

The Relation module has already extended the EFQ class to encapsulate querying Relations; eg you can easily query for "Find me all Engagements for Event with some entity id". You can additionally attach drupal Fields to endpoints and query those fields in your EFQ-Relation query object.

I imagine you could loop through your existing database and build the necessary Relation(s) to migrate the data into "Relation" relations.

There are also Views integration with Relation .... but I personally stick with EFQ for alot of stuff.

Just my 2 cents ...

EDIT

there is a patch to allow the use of the migrate module to move Reference fields into Relations.

Answer 2

So each Event node has an entity reference to Engagement, and you wish to find all Engagement where the Event is within a date range?

Have you considered just query for the Event and load all its references to Engagement nodes into an unique array?

For example:

$query = new EntityFieldQuery();
$results = $query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'event')
  ->fieldCondition(<date_range_condition>)
  ->execute();

$engagements = array();
foreach ($results['node'] as $nid => $result) {
    $event = node_load($nid);
    $engagement_nid = $event->field_reference[LANGUAGE_NONE][0]['target_id'];
    $engagements[$engagement_nid] = $engagement_nid;
}

Not ideal if the sets are really really large, but I find that reasonable sizes are ok, like when you can use range to limit the size. Depending on your use case, I find it nicer to sacrifice a bit of performance and not having to tinker around with classes and keep things as simple as possible, especially when performance loss is negligible.