1

I have a table products structure like this

product_id   product_name   product_created_date   product_expiry_date  availability  
----------   ------------   -------------------    -------------------  ------------
    1           x             12-NOV-13                14-JUN-14               No
    2           y             13-DEC-13                   null                Yes
    3           z             14-DEC-14                14-DEC-15              Yes
    1           p             12-NOV-13                14-JUN-14              Yes 
    ----        --             -----                     ----               ----      

I need a query like :

select product_id from products where (product_expiry_date>='12-DEC-14' or product_expiry_date is null) and availability='Yes'

how to do this using EntityFieldQuery?

4
  • 1
    Is that a table used from an entity. If it is not an entity, you cannot use EntityFieldQuery.
    – apaderno
    Dec 11 '14 at 6:19
  • Yes, this table is from entity and i am able to retrive them without null, but i need that null records as well Dec 11 '14 at 6:28
  • Please take a look to this post drupal.stackexchange.com/questions/14499/… about using OR in EFQ. :-)
    – Guardian
    Dec 11 '14 at 8:24
  • Its not possible. Check this post drupal.stackexchange.com/questions/14499/… "It is not possible, if not subclassing EntityFieldQuery, and overriding some methods." "One way round it might be to add a tag to the query with with ->addTag(), then implement hook_query_TAG_alter() to change the internal structure of the query manually for queries containing that tag." Dec 11 '14 at 8:35
0

I had to do a query like that once, here is how I achieve this with EFQ:

$matches = array();
yourmodule_mini_query('12-DEC-14', $matches);
yourmodule_mini_query('null', $matches);

function yourmodule_mini_query($value, &$matches) {
  $entityQuery = new EntityFieldQuery();
  $query = $entityQuery->entityCondition('entity_type', 'product');
  $query->fieldCondition('product_expiry_date', 'value', $value, '=');
  $query->fieldCondition('availability', 'value', 'Yes', '=');
  $results = $query->execute();

  if(count($results)) {
    foreach ($results['product'] as $p) {
      // By using product_id as array key, you deal with the duplicates
      $matches[$p->product_id] = $p->product_id;
    }
  }
}

Where $matches contains an array of product_id's that are available and for which product_expiry_date is null OR 12-DEC-14.

Hope it helped.

EDIT: As said in the comments below, the evaluation of the NULL value may require a second distinct query instead of calling twice the same function.

2
  • Isn't this second call to the mini query function going to query for fields with value as "null" string (I.e instead of an IS NULL, it queries value='null'.
    – AKS
    Dec 11 '14 at 14:42
  • Oh, actually, I haven't thought about that null value but indeed, it surely will. Maybe two distinct queries are inevitable here... Thanks for the tip :p
    – Djouuuuh
    Dec 11 '14 at 14:47

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