How do I query for the next three dates using EntityFieldQuery or Views? (7)

Question

This is my first question, so please don't be too harsh. I've been trying for the last couple days to solve this with no luck.

I have a content type called "Event" which contains a custom date field for the 'start date/time' of the event. What I need is to be able to list events for the next THREE days after the current timestamp.

Keep in mind that it doesn't count empty days, so if there are no events for tomorrow, for example, it won't count that as an empty day. Instead it would list the events out for the next day, and the following (assuming there are events for those days).

If I were able to access the custom date field through straight SQL, I would first get the 3 dates I need, and the query would look something like this:

SELECT DISTINCT(DATE(`date_field`)) 
FROM {node} 
WHERE `type` = 'event' 
AND `field_start_test_date` > NOW() 
ORDER BY DATE(`date_field`) ASC 
LIMIT 3;

I've been unable to accomplish this with Views or EntityFieldQuery. I'm also willing to create my own joins using SQL, but from what I've read, that's discouraged. I'm open to any suggestions. Please let me know if I need to be more specific.

Thanks!

Answer 1

The query you've got in your question would be the equivalent of this EntityFieldQuery:

$query = new EntityFieldQuery;
$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'event')
  ->fieldConditon('field_start_test_date', 'value', REQUEST_TIME, '>')
  ->fieldOrderBy('field_start_test_date')
  ->range(0, 3);

$results = $query->execute();
if (!empty($results['node'])) {
  $nodes = node_load_multiple(array_keys($results['node']));
}

I've assumed that date_field was supposed to be field_start_test_date and that the date column is a UNIX timestamp type. You'll need to make a couple of slight changes if those assumptions aren't right.

Answer 2

I'm just now getting back to this. I found what I believe to be the most elegant solution to my problem.

I had to break it down into two parts. The first thing I needed to do was get the next three days worth of events. I had to do it with this MySQL query:

$dates_query =
"   
SELECT  
    DISTINCT(DATE({field_start_date}.field_start_date_value)) AS start_date 
FROM 
    {node} 
    INNER JOIN {field_body} ON ({field_body}.revision_id = {node}.vid) 
    INNER JOIN {field_start_date} ON ({field_start_date}.revision_id = {node}.vid) 
WHERE 
    {node}.status = 1 AND
    {node}.type = 'event' AND 
    {field_start_date}.field_start_date_value >= CURDATE() 
ORDER BY {field_start_date}.field_start_date_value ASC 
LIMIT 3;
;"; 

That query should retreive the next three dates (if any) that contain events. From there, I had to loop through the dates and pass them through this function which retreives their events using EntityFieldQuery:

function getEventsByDate($date)
{
    //Function to return a list of events for a specific date using EntityFieldQuery

    //start and end timestamps to query between (MySQL format)
    $start = date('Y-m-d', strtotime($date));
    $end = date('Y-m-d', strtotime($date . ' +1 day'));

    $events_query = new EntityFieldQuery;
    $events_query           
        ->entityCondition('entity_type', 'node')
        ->entityCondition('bundle', 'event') //get content types of event
        ->propertyCondition('status', 1) //published nodes only
        ->fieldCondition('field_start_test_date', 'value', array($start, $end), 'BETWEEN')
        ->fieldOrderBy('field_start_test_date', 'value', 'ASC');
    $events_results = $events_query->execute();

    return $events_results; 
}

Thank you, clive for your help with this.