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...etcSchedules table: start and end date with repeat optionsDates table: the actual dates of the event created from the data inSchedules
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;
}
