1

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!

  • What you want to accomplish is simple, but the way I read the question, it is ambiguous. The SQL you've written will fetch the next three events, which is different from events during the next three days. Please clarify which it is you want to accomplish. – Letharion Jun 27 '12 at 7:55
  • Sorry for the confusion. The two different fields I have there should actually be the same. We'll go with 'date_field' for clarity. I need the events for the next three days. However, that query should only return the next three dates which contain events (DISTINCT(DATE(date_field)) should take of this). From there I was going to run a second query which pulled back records with those date_field matched those dates. Thanks again! I appreciate all the help I can get. – Richard Robinson Jun 27 '12 at 14:57
2

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.

  • Clive, I did make a couple adjustments to make it functional. However, it didn't return the results I need. This query grabs the next three events. What I'm looking for is the next three DATES that contain events. This can be done in MySQL by selecting DISTINCT(DATE(date_field)) FROM events LIMIT 3. Do you know if there is something equivalent to that in EntityFieldQuery? Thanks again for your help here – Richard Robinson Jun 27 '12 at 15:46
  • Ah ok; I think you're going to struggle with an EntityFieldQuery for that to be honest...it's used specifically to return the ids of entities (nodes/users/etc), which you then use to load up the entity objects and do something with them. You could alter the query (in hook_query_alter()) but I don't think you'll get it to do what you want. I think you're going to need to go directly in to the DB tables for this (assuming you're using an SQL field storage engine). You'll want to use the field_revision_field_* tables rather than the field_revision_data_* ones if that helps – Clive Jun 27 '12 at 15:52
  • Thanks! It's good to at least know the right direction. I'm not sure I follow you when you talk about the two different tables though. – Richard Robinson Jun 27 '12 at 17:17
  • Yeah it's a bit confusing unless you dig into the field API; field data is stored in two separate tables...one is the data table, the other the revision table (the data in these two is often identical, especially if you don't have any entities that are using revisions). Either way the field API uses the field_revision_* tables to get the freshest data, so if you're going directly into the database it would be wise to follow suit – Clive Jun 27 '12 at 17:26
  • Using the EntityFieldQuery class is the way to go. Fields can use different storages, and you cannot rely on the fact they are saved in a table of the active database. – kiamlaluno Jul 5 '12 at 15:50
1

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.

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.