This relates to a classified listings site, where nodes have an expiry date (date, field_period, start/end = value/value2) and a notifications field (text, checkboxes, 336=two weeks, 672=four weeks).

We want to query for nodes which are going to expire between two and four weeks from now -

$period = 672;
$paid_date_max = date('Y-m-d H:i', strtotime('+' . $period . ' hours'));
$paid_date_min = date('Y-m-d H:i', strtotime('+' . $period/2 . ' hours'));

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'entry')
  ->propertyCondition('status', 1)
  ->fieldCondition('field_period', 'value2', $paid_date_max, '<')
  ->fieldCondition('field_period', 'value2', $paid_date_min, '>=')
  ->fieldCondition('field_period', 'value2', 'NULL', '!=')
  ->addMetaData('account', user_load(1));
$result = $query->execute();

That works fine, but includes nodes which have already been sent a reminder (in which case field_expiry_notifications will contain a value matching $period). field_expiry_notifications may have multiple values, so we need to accept only nodes where NONE of the field_period_notifications values match $period.

Q: How to exclude from the above resultset any nodes with a matching expiry_notifications value?

  • On trying to express this in SQL I realised it might not be possible without subqueries, so maybe this isn't a supported use. Worth throwing a bounty at anyway :) – Chris Burgess Jan 29 '13 at 22:59

I believe you do need a subquery here. Apparently, you can actually do that with EntityFieldQuery. So first create your subquery, something like this:

$subquery = db_select('your_field_data_table', 't');
$subquery->addField('t', 'nid');
$subquery->condition('field_period_notifications', $period);

That gives you all nodes that have a matching value. Then add this to your EntityFieldQuery to exclude those:

->fieldCondition('field_period', 'nid', $subquery, 'NOT IN')

Or maybe you can do:

->entityCondition('entity_id', $subquery, 'NOT IN')
| improve this answer | |

Sounds like what you need is an OR query (but also an AND); Look into altering the query after making it. This is probably the better explanation of that I've seen: http://treehouseagency.com/blog/fredric-mitchell/2012/02/21/or-queries-entityfieldquery

| improve this answer | |

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.