EntityFieldQuery vs Db_select()

Question

Why should I use EntityFieldQuery when I can do the same work with Db_select() to fetch the value.

It would be better if someone can provide an example, not only a link.

Answer 1

I think the point is that the syntax is much simpler, and the code will be more understandable.

For example, if you want nodes with type my_type that have a field named field_foo with the value $val, with Db_Select, yuoll do something like :

$nids = db_select('node', 'n')
  ->fields('n', array('nid'))
  ->join('field_data_field_foo', 'foo', 'foo.entity_id = n.nid')
  ->condition('n.type', 'my_type')
  ->condition('foo.field_foo_value', $val)
  ->execute()->fetchCol();

Which is much simpler with EntityFieldQuery :

$query = new EntityFieldQuery;
$entities = $query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'my_type')
  ->fieldCondition('field_foo', 'value', $val)
  ->execute();

Answer 2

I think that the main reason prefering EntityFieldQuery over db_select is that you don't have to know about the lower-level structure, in other words: how stuff is stored in the database. This improves loose coupling.

Answer 3

EntityFieldQuery (EFQ) will only return entity IDs. If you want to access entities data, you'll have to call entity_load(), which, among loading the data, will make sure that all underlying stuff you normally do not care about (such as loading fields, calling other modules hooks etc.) are made. Of course, this result in two SQL queries and a lot of overhead, but this is the price to pay for the abstraction.

As for the EFQ syntax being more clear, I think it's much more a question of personal preferences. I, for instance, do not think that EFQ is clearer. Notice that a working db_select() replacement with EFQ must include the return value test and subsequent entity_load() call, and this adds a lot of noise to the code, IMHO:

$query = new EntityFieldQuery();
$entities = $query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'my_type')
  ->fieldCondition('field_foo', 'value', $val)
  ->execute();
if (!empty($entities['node'])) {
  $nodes = entity_load('node', array_keys($entities['node']));
} else {
  $nodes = array();
}

So, answering your question: use EFQ if your entities are full-featured (e.g. are fieldable, can be used by other modules, etc.) and/or you think that its syntax is clearer. If other cases, use may use db_select().

Answer 4

EntityFieldQuery is far more limited than db_select(), so you should have a really good reason to not use db_select() (see bart answer), which is readable enough and far more flexible.

For example, entityFieldQuery use innerJoin to fetch fields. If you need a leftJoin for whatever reason, you're trapped... http://drupal.org/node/1226622