mysql - Select All Events with Event->Schedule->Date between start and end dates in CakePHP

Question

Trying to figure out how to build a query in CakePHP where I can select all Events that are between X and Y dates (user-entered dates).

The problem lies in that the Event doesn't have the dates in it's table.

Event hasMany Schedule
Schedule belongsTo Event

Schedule hasMany Date
Date belongsTo Schedule

• Events table: details of the event - name, location, description...etc
• Schedules table: start and end date with repeat options
• Dates table: the actual dates of the event created from the data in Schedules

So - I actually need to select any Events that have at least one Date entry between the X and Y dates.

I also need to be able to display the dates with the event data.

---

Edit (REVISED):

I've tried this, but it appears to be retrieving the events regardless of the Date, but only retrieving the Date info if the date falls within the range:

$this->Event->Behaviors->attach('Containable');
$events = $this->Event->find('all', array(
    'limit'=>5,
    'order'=>'Event.created DESC',
    'contain' => array(
    'Schedule' => array(
        'fields'=>array(),
        'Date' => array(
            'conditions'=>array(
                'start >=' => $start_date,
                'start <=' => $end_date,
                )
            )
        )
    ),
));

*Just to clarify - Date.start and Date.end are always the same Date - they just also include a time (both datetime fields) - hence why I'm checking "start" against both.

---

I've tried using containable, I've tried unbind/bindModel..etc - I must be doing something wrong or off-track.

Something to keep in mind - once I figure out how to get the Events based on the Date, I also need to add on other conditions like Event Types and more - not sure if this would affect the answer(s) or not.

---

UPDATE:

Here's what I'm using that seems to work - also seems very ugly - any thoughts?:

function getEvents($opts = null) {
    //$opts = limit, start(date), end(date), types, subtypes, subsubtypes, cities

    $qOpts['conditions'] = array();

    //dates
    $qOpts['start'] = date('Y-m-d') . ' 00:00:00';
    if(isset($opts['start'])) $qOpts['start'] = $opts['start'];

    $qOpts['end'] = date('Y-m-d') . ' 23:59:59';
    if(isset($opts['end'])) $qOpts['end'] = $opts['end'];

    //limit
    $qOpts['limit'] = 10;
    if(isset($opts['limit'])) $qOpts['limit'] = $opts['limit'];

    //fields
    //$qOpts['fields'] = array('Event.id', 'Event.name', 'Event.slug', 'City.name', 'Date.start');  
    // if(isset($opts['fields'])) $qOpts['fields'] = $opts['fields'];


    //date conditions
    array_push($qOpts['conditions'], array(
        "Date.start >=" => $qOpts['start'],
        "Date.start <=" => $qOpts['end'],
    ));

    //cities conditions
    if(isset($opts['cities'])) {
        if(is_array($opts['cities'])) {
            $cityConditions['OR'] = array();
            foreach($opts['cities'] as $city_id) {
                array_push($cityConditions['OR'], array('OR'=>array('Venue.city_id'=>$city_id, 'Restaurant.city_id'=>$city_id)));
            }
            array_push($qOpts['conditions'], $cityConditions);
        }
    }

    //event types conditions
    //$opts['event_types'] = array('1');
    if(isset($opts['event_types'])) {
        if(is_array($opts['event_types'])) {
            $eventTypeConditions['OR'] = array();
            foreach($opts['event_types'] as $event_type_id) {
                array_push($eventTypeConditions['OR'], array('EventTypesEvents.event_type_id' => $event_type_id));
            }
            array_push($qOpts['conditions'], $eventTypeConditions);
        }
    }

    //event sub types conditions
    if(isset($opts['event_sub_types'])) {
        if(is_array($opts['event_sub_types'])) {
            $eventSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_types'] as $event_sub_type_id) {
                array_push($eventSubTypeConditions['OR'], array('EventSubTypesEvents.event_sub_type_id' => $event_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubTypeConditions);
        }
    }

    //event sub sub types conditions
    if(isset($opts['event_sub_sub_types'])) {
        if(is_array($opts['event_sub_sub_types'])) {
            $eventSubSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_sub_types'] as $event_sub_sub_type_id) {
                array_push($eventSubSubTypeConditions['OR'], array('EventSubSubTypesEvents.event_sub_sub_type_id' => $event_sub_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubSubTypeConditions);
        }
    }


    $this->recursive = 2;

    $data = $this->find('all', array(
        'contain' => array(
            'Restaurant' => array(
                'fields' => array('id', 'name', 'slug', 'address', 'GPS_Lon', 'GPS_Lat', 'city_id'),
                'City' => array(
                    'fields' => array('id', 'name', 'url_name'),
                ),
            ),
            'Venue' => array(
                'fields' => array('id', 'name', 'slug', 'address', 'GPS_Lon', 'GPS_Lat', 'city_id'),
                'City' => array(
                    'fields' => array('id', 'name', 'url_name')
                )
            ),
            'Schedule' => array(
                'fields' => array('id', 'name'),
                'Date' => array(
                    'fields' => array('start', 'end'),
                    'conditions' => array(
                        'Date.start >=' => $qOpts['start'],
                        'Date.start <=' => $qOpts['end'],
                    ),
                ),
            ),
            'EventType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
            'EventSubType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
            'EventSubSubType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
        ),
        'joins' => array(
            array(
                'table' => $this->Schedule->table,
                'alias' => 'Schedule',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'Schedule.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->Schedule->Date->table,
                'alias' => 'Date',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'Date.schedule_id = Schedule.id',
                ),
            ),
            array(
                'table' => $this->EventTypesEvent->table,
                'alias' => 'EventTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventTypesEvents.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->EventSubTypesEvent->table,
                //'table' => 'event_sub_types_events',
                'alias' => 'EventSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubTypesEvents.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->EventSubSubTypesEvent->table,
                'alias' => 'EventSubSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubSubTypesEvents.event_id = Event.id',
                ),
            ),
        ),
        'conditions' => $qOpts['conditions'],
        'limit' => $qOpts['limit'],
        'group' => 'Event.id'
    ));
    return $data;
}

Answer 1

In this kind of situation, I tend not to use Cake's associations, or Containable, and craft the joins myself:

$events = $this->Event->find('all', array(
    'joins'=>array(
        array(
            'table' => $this->Schedule->table, 
            'alias' => 'Schedule', 
            'type' => 'INNER', 
            'foreignKey' => false,
            'conditions'=> array(
                'Schedule.event_id = Event.id',
            ),
        ),
        array(
            'table' => $this->Date->table, 
            'alias' => 'Date', 
            'type' => 'INNER', 
            'foreignKey' => false,
            'conditions'=> array(
                'Date.schedule_id = Schedule.id',
            ),
        ),
    ),
    'conditions'=>array(
        'Date.start >=' => $start_date,
        'Date.start <=' => $end_date,
    ),
    'order'=>'Event.created DESC',
    'limit'=>5
));

It's a bit chunky, but results in the exact query I want.

UPDATE

Let's break your code in parts and see where we could improve it. The first part is the preparation for the find. I've rewritten your code trying to make it shorter, and this is what I came up with:

// Default options go here
$defaultOpts = array(
    'start' => date('Y-m-d') . ' 00:00:00',
    'end' => date('Y-m-d') . ' 23:59:59',
    'limit' => 10
)

// Use default options if nothing is passed, otherwise merge passed options with defaults
$opts = is_array($opts) ? array_merge($defaultOpts, $opts) : $defaultOpts;

// Initialize array to hold query conditions
$conditions = array();

//date conditions
$conditions[] = array(
    "Date.start >=" => $qOpts['start'],
    "Date.start <=" => $qOpts['end'],
));

//cities conditions
if(isset($opts['cities']) && is_array($opts['cities'])) {
    $conditions['OR'] = array();
    $conditions['OR'][] = array('Venue.city_id'=>$opts['cities']);
    $conditions['OR'][] = array('Restaurant.city_id'=>$opts['cities']);
}

//event types conditions
//$opts['event_types'] = array('1');
if(isset($opts['event_types']) && is_array($opts['event_types'])) {
    $conditions[] = 'EventTypesEvents.event_type_id' => $opts['event_types']
}

//event sub types conditions
if(isset($opts['event_sub_types']) && is_array($opts['event_sub_types'])) {
    $conditions[] = 'EventSubTypesEvents.event_sub_type_id' => $opts['event_sub_types']
}

//event sub sub types conditions
if(isset($opts['event_sub_types']) && is_array($opts['event_sub_sub_types'])) {
    $conditions[] = 'EventSubSubTypesEvents.event_sub_sub_type_id' => $opts['event_sub_sub_types']
}

Notice that I eliminated most of the ORs. That's because you can pass an array as a value in conditions, and Cake will make it an IN(...) statement in the SQL Query. For example: 'Model.field' => array(1,2,3) generates 'Model.field IN (1,2,3)'. This works just like ORs, but requires less code. So the code block above does exactly the same your code was doing, but it's shorter.

Now comes the complex part, the find itself.

Usually I'd recommend the forced joins alone, without Containable, and with 'recursive'=>false. I believe this usually is the best way to deal with complex finds. With Associations and Containable, Cake runs several SQL queries against the database (one query per Model/table), which tends to be inefficient. Also, Containable not always returns the expected results (as you noticed when you tried it).

But since in your case there are four complex associations involved, maybe a mixed approach will be the ideal solution - otherwise, it would be too complicated to clean-up the duplicate data. (The 4 complex associations are: Event hasMany Dates [through Event hasMany Schedule, Schedule hasMany Date], Event HABTM EventType, Event HABTM EventSubType, Event HABTM EventSubSubType). So, we could let Cake handle data retrieval of EventType, EventSubType and EventSubSubType, avoiding too many duplicates.

So here is what I suggest: use joins for all the required filtering, but do not include Date and [Sub[Sub]]Types in fields. Because of the model associations you have, Cake will automatically run extra queries against the DB to fetch those bits of data. No Containable needed.

The code:

// We already fetch the data from these 2 models through
// joins + fields, so we can unbind them for the next find,
// avoiding extra unnecessary queries. 
$this->unbindModel(array('belongsTo'=>array('Restaurant', 'Venue'));

$data = $this->find('all', array(
    // The other fields required will be added by Cake later
    'fields' => "
        Event.*, 
        Restaurant.id, Restaurant.name, Restaurant.slug, Restaurant.address, Restaurant.GPS_Lon, Restaurant.GPS_Lat, Restaurant.city_id,
        Venue.id, Venue.name, Venue.slug, Venue.address, Venue.GPS_Lon, Venue.GPS_Lat, Venue.city_id,
        City.id, City.name, City.url_name
    ",  
    'joins' => array(
        array(
            'table' => $this->Schedule->table,
            'alias' => 'Schedule',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'Schedule.event_id = Event.id',
        ),
        array(
            'table' => $this->Schedule->Date->table,
            'alias' => 'Date',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'Date.schedule_id = Schedule.id',
        ),
        array(
            'table' => $this->EventTypesEvent->table,
            'alias' => 'EventTypesEvents',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'EventTypesEvents.event_id = Event.id',
        ),
        array(
            'table' => $this->EventSubSubTypesEvent->table,
            'alias' => 'EventSubSubTypesEvents',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'EventSubSubTypesEvents.event_id = Event.id',
        ),
        array(
            'table' => $this->Restaurant->table,
            'alias' => 'Restaurant',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Event.restaurant_id = Restaurant.id',
        ),
        array(
            'table' => $this->City->table,
            'alias' => 'RestaurantCity',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Restaurant.city_id = city.id',
        ),
        array(
            'table' => $this->Venue->table,
            'alias' => 'Venue',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Event.venue_id = Venue.id',
        ),
        array(
            'table' => $this->City->table,
            'alias' => 'VenueCity',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Venue.city_id = city.id',
        ),
    ),
    'conditions' => $conditions,
    'limit' => $opts['limit'],
    'recursive' => 2
));

We eliminated contains, and some of the extra queries Cake was running because of it. Most joins are of type INNER. This means that at least one record must exist on both tables involved in the join, or you'll get less results then you'd expect. I'm assuming each Event takes place at a Restaurant OR a Venue, but not both, that's why I used LEFT for those tables (and cities). If some of the fields used in the joins are optional, you should use LEFT instead of INNER on the related joins.

If we used 'recursive'=>false here, we'd still get the right events, and no data repetition, but dates and [Sub[Sub]]Types would be missing. With the 2 levels of recursion, Cake will automatically loop through the returned events, and for each event it will run the necessary queries to fetch the associated model data.

This is almost what you were doing, but without Containable, and with a few extra tweaks. I know it's still a long, ugly and boring piece of code, but after all there are 13 database tables involved...

This is all untested code, but I believe it should work.