2

We have a date field attached to the user entity. It stores values for year, month and day, in the following format (looking at the field_date_of_birth_value column in the DB): 1987-06-08T00:00:00.

We are using EFQ and wish to return users who's birthday is today OR some other conditions on another field also attached to the user entity (field_babys_date_of_birth, which doesn't necessarily have a value for every user and has is stored in the same format as the other field).

Is there a way to compare the stored month and day with the current ones using EFQ? If not, how would you approach this problem?

Pseudocode specification

  FETCH every account where:
    date_of_birth == TODAY (month and day only)  OR
    babys_date_of_birth == TODAY + 6 MONTHS OR
    babys_date_of_birth == TODAY + 3 MONTHS
  END FETCH
  FOREACH accounts as account
    // Custom logic (always the same).
  END FOREACH

Implemented code

<?php
// Fetch users whose birthday is today.
$today = new DateTime('today');
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_date_of_birth', 'value', $today, '=');
$result = $query->execute();
$uids = array_keys($result['user']);
foreach ($uids as $uid) {
  $account = user_load($uid);
  // Custom logic (always the same).
}

// Fetch users where babys_date_of_birth == today + 3months.
$after_three_months = new DateTime('today + 3 months');
$after_three_months = $after_three_months->format('Y-m-d\T00:00:00');
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_babys_date_of_birth', 'value', $after_three_months, '=');
$result = $query->execute();
$uids = array_keys($result['user']);
foreach ($uids as $uid) {
  $account = user_load($uid);
  // Custom logic (always the same).
}

// Fetch users where babys_date_of_birth == today + 6 months.
$after_six_months = new DateTime('today + 6 months');
$after_six_months = $after_six_months->format('Y-m-d\T00:00:00');
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_babys_date_of_birth', 'value', $after_six_months, '=');
$result = $query->execute();
$uids = array_keys($result['user']);
foreach ($uids as $uid) {
  $account = user_load($uid);
  // Custom logic (always the same).
}
?>

Known problems with the code:

  1. We think it should be better to have only one query to return all the accounts, as the custom logic to be executed is the same for all of them.
  2. The first condition should only check for month and day (excluding year).

Maybe EFQ doesn't support ORed conditions, and we should use something else?

  • Please show the code you're using – Clive Jan 23 '13 at 14:31
2

About using OR conditions, you can read the answers for Using OR with EntityFieldQuery.

For the code to use to check only the month, and the day, I would use the following query.

$today = new DateTime('today');
$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_babys_date_of_birth', 'value', $today->format('-m-d\T'), 'CONTAINS');

Basing on EntityFieldQuery::addFieldCondition(), the method called by EntityFieldQuery::addFieldCondition(), $operator can be one of the following: =, <, >, <=, >=, STARTS_WITH, CONTAINS, IN, NOT IN, BETWEEN.

If LIKE is supported, I would write the field condition as:

$query->entityCondition('entity_type', 'user')
  ->fieldCondition('field_babys_date_of_birth', 'value', '%' . db_like($today->format('-m-d\T')) . '%', 'LIKE');
| improve this answer | |
  • This seems like the best approach so far. However, messing with EFQ to workaround ORed queries scares me out :) Given that we won't be using anything but MySql compatible DBs in the foreseeable future, how about this other option: doing a db_select query over each field table to get all matching user entity IDs and then do a user_load_multiple and execute our custom logic on each of them. Like that better for simplicity? – rafamd Jan 23 '13 at 20:54
  • I think that the only problem would be with field storage implementations that don't use a SQL database engine, such as in the case of Mongodb; passing from MySQL to PostgreSQL should not cause any problem. The alternative solution (using hook_query_alter() as suggested from the answer to the other question) would probably have problems with Mongodb too. – kiamlaluno Jan 23 '13 at 22:33
2

If you want to filter in your efq, you'll have to compare strings:

   $today = new DateTime();
   $today = $today->format('Y-m-d\T00:00:00');

Then add in your query the condition

'field_date_of_birth_value', $today, '='

I can't tell more unless you show us your code.

| improve this answer | |
  • That's what we are looking for, except that we need to compare just the month and day (we want user's who's birthday is today, so the year doesn't matter). – rafamd Jan 23 '13 at 14:49
  • 1
    you can do a for loop on the result set and then compare and compare the date like this. if $today->format('m-d') == substr(dategotfromdb, 5, 4); – Mohammed Shameem Jan 23 '13 at 15:16
  • Arf sorry ; so you will indeed need to make a loop, as @MohammedShameem advised... But it will be after the query ! Plz show your code, we will help better. – Gregory Kapustin Jan 23 '13 at 15:20
  • Doesn't seem optimal at all, we would need to retrieve the entire userbase and do the check in the loop. I've uploaded the code, maybe that helps. Maybe we need to look beyond EFQ ? – rafamd Jan 23 '13 at 16:41
  • Indeed, the problem we have here is that it can be any year... My guess would be to store 'somewhere' the day and month of birth per user, like after a hook_user_update. Call that hook, get the birth date, extract day and month from it, store that in a new hidden field or in a new table. Then your query will be easy as pie. – Gregory Kapustin Jan 23 '13 at 16:46

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.