2

A taxonomy has a lot of big fields attached, but one of these fields is a short text field called external_identifier.

In code, I need to obtain an array mapping external_identifiers to tids for a given list of external identifiers.

So in raw SQL this is very efficient:

SELECT entity_id, field_external_identifier_value
  FROM field_data_field_external_identifier
 WHERE field_external_identifier_value IN ('a123','b567',...);

I understand I should be using EntityFieldQuery, but this only gives me an array of tids, keyed by ...tid. Then if I loop those and call entity_load it will be doing lots of separate lookups and loading a whole load of stuff I don't need. This looks really inefficient.

I understand that EntityFieldQuery will only return Ids, so is there a better way?

Or do I just resort to SQL?

  • When the built-in tools don't cover your edge case - always go with raw SQL :) An EntityFieldQuery isn't supposed to query for field data (despite the name), it's only supposed to query for entities. What you decide to do with those entities, e.g. fully load them to get access to their own field data, is outside the scope of an EFQ – Clive Aug 15 '13 at 14:34
  • entity_load takes an array of id's. You don't have to load them individually in a loop. Just loading them all at once is enough. – Beebee Aug 15 '13 at 14:35
  • @Beebee It's still massively inefficient when compared to a single query - each attached field becomes another JOIN in the query, and it's all or nothing. Plus entity load hooks get called which incur extra overhead. With custom SQL you can join only the tables you need for a particular query. I'm a big advocate of using the API, but not for something like this, it's just too inefficient – Clive Aug 15 '13 at 14:37
  • 1
    I see what you mean @Clive, I was only responding to "... Then if I loop those and call entity_load it will be doing lots of separate lookups...". I was just pointing out that there's no need to loop and load, you can load and then loop. But I can see what you mean by not being efficient. I myself use db_query and db_select most of the time. – Beebee Aug 15 '13 at 14:44
  • 1
    Pluggable field storage means that there is no guarantee that the field is even in the primary database or even in SQL. – mpdonadio Aug 15 '13 at 16:25
4

The safest way to get a field value in Drupal is to load the whole entity using the API - Drupal is a modular system and this allows other modules to interact with the data. There are ways (see Clive's answer) to load the field with the API without having to load the full entity - but this is still much less efficient than a raw SQL query.

The question however is how often you need to run this inefficient code. Presumably you only need to run this code when entities have been modified - so while caching entities is not an option, you could well cache your map and only rebuild it when an entity has been modified (ie. on cache clear).

If your entities are modified very often, then rather then rebuild your map every time, you could use hook_entity_update and then edit that entitie's entry in your map - so continuously modify your map.

This approach may or may not be faster than using an SQL query every time you need your map - it depends on how often entities change and how often you generate the map.

That being said, it is extremely unlikely that Drupal 7's core database schema is going to change at this stage - you'd be quite safe using direct SQL queries (in particular if this is your own code as opposed to a module you want to release).

  • That's not totally true...you can get the value of a single field without having to load the entity (field_attach_load() has an option). It'll even work across multiple entities using a single query, so ends up being quite an attractive method. – Clive Aug 15 '13 at 15:44
  • 1
    @Clive : Thanks, I've edited my answer. This is not too important - my main point is that it may be possible to cache the generated map. – Alice Heaton Aug 15 '13 at 15:59
  • Cached map is a great idea! Couldn't see the wood for the trees. Thanks. – artfulrobot Aug 15 '13 at 16:04
3

You might be able to get something of a compromise using EntityFieldQuery and field_attach_load() (not sure if I've understood your problem 100% so can't be totally sure).

I'll leave the specific implementation to your imagination, but suffice to say you can use the EntityFieldQuery to query for the pertinent entity IDs, and then use field_attach_load() to load the field data, for a single field if necessary.

It avoids the need to load the entities, or any fields other than the one you're looking for. It's still not as efficient as raw SQL query, but is certainly more future-proof.

It would go something like...

// Result of the EFQ
$result = $query->execute();

if (!empty($result[$entity_type])) {
  $entities = $result[$entity_type];

  field_attach_load($entity_type, $entities, FIELD_LOAD_CURRENT, array('field_id' => $field_id));

  // Each $entity->$field_id should contain a relevant value if available.
}

As I say, might not work in your specific case but it's an interesting method nonetheless.

N.b. you'll probably need to use field_info_field() or field_info_instance() to get the ID of the field itself, so there'll be another query involved. Still nowhere near as much as a full entity load though.

  • Thanks, a combo of this and Alice's answer I think will be most future proof and Drupal-y way. As it happens this is actually a custom module that's not widely useful so I'll maintain the SQL but I'm doing more with entities and so this will be useful one day. Thanks for your time. PS. Accepted Alice's answer because had to choose one of you and with 57.4k points you don't really need more...! – artfulrobot Aug 15 '13 at 16:06
  • Oh definitely, this was just an after-thought, if I could've fit it in a comment I would have :) – Clive Aug 15 '13 at 16:14
  • @Clive Have you benchmarked this against field_get_items() on a loaded entity when you have entitycache enabled? – mpdonadio Aug 15 '13 at 17:15
  • @MPD 'Fraid not, I can't recall actually using this method myself other than testing that it worked when I first found it. That would definitely be an interesting reading to take; personally I've found that entitycache offers very little improvement, if any at all; but I've seen others saying it makes a good difference in their setup. On a very large site (300,000+ users with redis/memcache/varnish and all the trimmings) I've found it makes no difference at all, so under those circumstances I'd lean towards saying this method is quicker. But I have no hard data to back that up – Clive Aug 15 '13 at 17:25
2

Querying the database table is not an API. Not using an API means you are on your own and things might not behave/work as expected.

The values in there might or might not be the same as when you use entity_load(), various load hooks are executed that might affect the data that you actually get in the end.

Yes, entity_load() is considerably slower, but one way to solve this is to use the entitycache module, which makes loading entities much faster.

  • I take your point, but the inefficiency is massive and entitycache won't help - in my example of having large fields on a taxonomy, this data will never be needed for this use case - caching it is only going to make matters worse. The point of creating the map is to enable very fast lookups; I'll only need to access each term once, and then only to extract it's tid and this field. Thanks, tho'. – artfulrobot Aug 15 '13 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.