Here's the situation, which I suspect is pretty common:

  • A site is deployed where a node type has no fields added.
  • Lots of nodes are added.
  • The site is changed and a new field is added to the node type.

At this point, I've got my original nodes that do not have a value for the new field. There aren't even any field value records in the database for these nodes.

I now need to query for the nodes where this field is set to a certain value (a new or updated node) OR where it's one of the original nodes. So I'm thinking something like this (note the NULL in my array of possible values):

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'my_content_type')
  ->fieldCondition('field_myfield', 'value', array('myvalue', NULL));

Unfortunately, this doesn't work, and I suspect it's due to the nature of the join. Is it possible to return results where the field is a certain value OR where there is no record of a value at all?

ps: how do I see/debug the resulting query?

  • @clive Thanks for the debug link. Now that I can see the query, I realize that the problem is much different than I originally thought. I'm unable to query for the original nodes using EFQ (as they have no entry in the field table). I had suspected it was because of the type of JOIN EFQ was using, but now that I look the query, I see that there is no JOIN at all, it is simply querying the field_data_field_myfield table. It doesn't JOIN node at all. Is rolling my own SQL query that selects from node and joins the field table my best option here?
    – rcourtna
    Mar 18, 2013 at 15:34

1 Answer 1


Although EFQ is a good tool, it starts with the assumption that you are querying about a field value. It does not do the join because it is more efficient to get what you want the way it does it. For you legacy nodes, I think you should do a legacy Dynamic Query. This way you can specify a Left Join to get a result set that includes nodes with NULL/absent entries in the field table.

$query = db_select('node', 'n');
$query->leftJoin('field_data_field_myfield', 'f', 'n.nid = f.entity_id');
$query->condition('f.value', array('myvalue', NULL), 'IN')
  ->condition('n.type', 'my_content_type');

Joins in the dynamic query method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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