php mysql cakephp associations cakephp-1.3

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

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;
}

Solution

GROUP_CONCAT to the rescue!!! Long story short - I needed to return Events with their many Dates (with being able to query against different HABTM tables) - but when I tried, I'd either get way too many events (one for each date...etc) or I'd use GROUP BY, and not get all the dates. The answer... still use GROUP BY, but combine the Dates into a single field using GROUP_CONCAT:

$qOpts['fields'] = array(
        ...
        'GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates'
    );

I'm posting a lot of code - feel free to browser if you got stuck like I did.

Things I learned:

It's NOT recommended to use contain AND join - pick one and stick with it - this was the bane of my existence for a bit - I'd get something to work, but then not w/ pagination..etc etc.
If you need to query based on HABTM data, pick join, not contain
My Joins were working just fine, but I would get the same event 10 times over (1 for each date that existed)
But when I tried to GROUP BY, it combined them, so I only got 1 date, when I really needed all the dates
GROUP_CONCAT is amazing (had never heard of it before)

Hope this helps someone. Feel free to point out any issues w/ my code - I always like to improve. But for now, I'm dancing in circles because it WORKS!!! Now that it works, I'm going to go back in and try to clean up those ORs like @bfavaretto mentioned.

    //returns events based on category, subcategory, and start/end datetimes
function getEvents($opts = null) {
    //$opts = limit, start(date), end(date), types, subtypes, subsubtypes, cities, paginate(0,1), venues, excludes(event ids)

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

    //order
    $qOpts['order'] = 'Date.start ASC';
    if(isset($opts['order'])) $qOpts['order'] = $opts['order'];

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

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

    //event excludes (example: when you want "other events at this venue", you need to exclude current event)
    if(isset($opts['excludes'])) {
        if(is_array($opts['excludes'])) {
            foreach($opts['excludes'] as $exclude_id) {
                array_push($qOpts['conditions'], array('Event.id <>' => $exclude_id));
            }
        }
    }

    //approval status conditions
    if(!isset($opts['approval_statuses'])) $opts['approval_statuses'] = array('1'); //default 1 = approved
    if(isset($opts['approval_statuses'])) {
        if(is_array($opts['approval_statuses'])) {
            $approvalStatusesConditions['OR'] = array();
            foreach($opts['approval_statuses'] as $status) {
                array_push($approvalStatusesConditions['OR'], array('Event.approval_status_id' => $status));
            }
            array_push($qOpts['conditions'], $approvalStatusesConditions);
        }
    }

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

    if(isset($opts['end'])) {
        array_push($qOpts['conditions'], array('Date.start <=' => $opts['end']));
        array_push($date_conditions, array('Date.start <=' => $opts['end']));
    }


    //venues conditions
    if(isset($opts['venues'])) {
        if(is_array($opts['venues'])) {
            $venueConditions['OR'] = array();
            foreach($opts['venues'] as $venue_id) {
                array_push($venueConditions['OR'], array('OR'=>array('Venue.id'=>$venue_id)));
            }
            array_push($qOpts['conditions'], $venueConditions);
        }
    }

    //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
    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);
        }
    }


    //joins
    $qOpts['joins'] = array();

    //Restaurants join
    array_push($qOpts['joins'], array(
            'table' => $this->Restaurant->table,
            'alias' => 'Restaurant',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Restaurant.id = Event.restaurant_id',
            ),
        )
    );

    //Venues join
    array_push($qOpts['joins'], array(
            'table' => $this->Venue->table,
            'alias' => 'Venue',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Venue.id = Event.venue_id',
            ),
        )
    );

    //Schedules join
    array_push($qOpts['joins'], array(
            'table' => $this->Schedule->table,
            'alias' => 'Schedule',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => array(
                'Schedule.event_id = Event.id',
            ),
        )
    );

    //Dates join
    array_push($qOpts['joins'], array(
        'table' => $this->Schedule->Date->table,
        'alias' => 'Date',
        'type' => 'INNER',
        'foreignKey' => false,
        'conditions' => array(
            'Date.schedule_id = Schedule.id',
            //$date_conditions
        ),
    ));

    //Uploads join
    array_push($qOpts['joins'], array(
            'table' => $this->Upload->table,
            'alias' => 'Upload',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Upload.event_id = Event.id',
            ),
        )
    );

    //Event types join
    if(isset($opts['event_types'])) {
        if(is_array($opts['event_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventTypesEvent->table,
                'alias' => 'EventTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }
    if(isset($opts['event_sub_types'])) {
        if(is_array($opts['event_sub_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventSubTypesEvent->table,
                'alias' => 'EventSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }
    if(isset($opts['event_sub_sub_types'])) {
        if(is_array($opts['event_sub_sub_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventSubSubTypesEvent->table,
                'alias' => 'EventSubSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubSubTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }

    $qOpts['fields'] = array(
        'Event.*',
        'Venue.id', 'Venue.slug', 'Venue.name', 'Venue.GPS_Lon', 'Venue.GPS_Lat',
        'Restaurant.id', 'Restaurant.slug', 'Restaurant.name', 'Restaurant.GPS_Lat', 'Restaurant.GPS_Lon',
        'GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates'
    );

    //group by
    $qOpts['group'] = 'Event.id';

    //you need to set the recursion to -1 for this type of join-search
    $this->recursive = -1;


    $paginate = false;
    if(isset($opts['paginate'])) {
        if($opts['paginate']) {
            $paginate = true;
        }
    }

    //either return the options just created (paginate)
    if($paginate) {
        return $qOpts;

    //or return the events data
    } else {
        $data = $this->find('all', $qOpts);
        return $data;
    }

}