Better way to retrieve HABTM, HasMany...etc data than -1 recursive manual joins in CakePHP?

Question

I have a database of Events. I created a model-function to retrieve the events based on city, type, subtype, start & end dates.

It's working (mostly), but geez, it's a novel worth of code. Is there a better way to do this? (It's a lot of code, but not hard to follow what I'm doing):

Some of my associations:

Event belongsTo Restaurant
Event belongsTo Venue
Restaurant belongsTo City
Venue belongsTo City

Event hasAndBelongsToMany EventType
Event hasAndBelongsToMany EventSubType

Event hasMany Schedule
Schedule hasMany Date

(and of course their belongsTo/hasMany counterparts are set up too)

My "getEvents" function (in the event model):

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

    //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
    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'];

    //joins
    $qOpts['joins'] = array(
        array('table' => 'schedules',
            'alias' => 'Schedule',
            'type' => 'LEFT',
            'conditions' => array(
                'Event.id = Schedule.event_id',
            )
        ),
        array('table' => 'dates',
            'alias' => 'Date', 
            'type' => 'LEFT',
            'order' => 'Date.start ASC',
            'conditions' => array(
                'Date.schedule_id = Schedule.id',
            ),
        ),
        array('table' => 'venues',
            'alias' => 'Venue',
            'type' => 'LEFT',
            'conditions' => array(
                'Event.venue_id = Venue.id',
            )
        ),
        array('table' => 'restaurants',
            'alias' => 'Restaurant',
            'type' => 'LEFT',
            'conditions' => array(
                'Event.restaurant_id = Restaurant.id',
            )
        ),
        array('table' => 'cities',
            'alias' => 'City',
            'type' => 'LEFT',
            'conditions' => array(
                'OR' => array(
                    'Venue.city_id = City.id',
                    'Restaurant.city_id = City.id',
                ),
            )
        ),
        array('table' => 'event_types_events',
            'alias' => 'EventTypesEvent',
            'type' => 'LEFT',
            'conditions' => array(
                'EventTypesEvent.event_id = Event.id',
            )
        ),
        array('table' => 'event_sub_types_events',
            'alias' => 'EventSubTypesEvent',
            'type' => 'LEFT',
            'conditions' => array(
                'EventSubTypesEvent.event_id = Event.id',
            )
        ),
        array('table' => 'event_types',
            'alias' => 'EventType',
            'type' => 'LEFT',
            'conditions' => array(
                'EventTypesEvent.event_type_id = EventType.id',
            )
        ),
        array('table' => 'event_sub_types',
            'alias' => 'EventSubType',
            'type' => 'LEFT',
            'conditions' => array(
                'EventSubTypesEvent.event_sub_type_id = EventSubType.id',
            )
        ),
    );

    //date conditions
    $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('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('EventType.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('EventSubType.id'=>$event_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubTypeConditions);
        }
    }

    $this->recursive = -1;
    $data = $this->find('all', $qOpts);
    return $data;
}

Bonus points: I have a question on StackOverflow (here) that asks about this function as well - trying to figure out how to get it to return multiple dates instead of just one per event.

Sorry, not getting it. If your associations are properly set up Cake will do all the joining and fetching automatically **if you set `recursive` to something > 0**. Why are you *disabling* all this automagic? — deceze, Jul 02 '11 at 03:14
@deceze - either a) I'm a complete moron (could very well be the case), or b) it was returning way too much data, which led me down this path or c) both a and b. But - your suggestion makes me think I can just use the normal "find" and pass the fields I want? If it works, this will have been an EPIC fail on my part! :) — Dave, Jul 02 '11 at 03:15
Indeed. :o) Scratch all that code and do a normal `find`. You'll probably get a lot of data for each event. If you want to limit that, look into the ContainableBehavior. If you have more complex conditions that require you to join tables, ask more specific questions about that. — deceze, Jul 02 '11 at 03:18
@deceze - Do you want to answer the question "Dave, you moron, just use a normal 'find'!", or shall I delete this question? (and thank you very much for leading me back to reality!) — Dave, Jul 02 '11 at 03:20

score 2 · Answer 1 · edited Dec 19 '21 at 09:40

2

Just set up your associations properly and do a normal (recursive) find(). If you want more control over the associated data that's retrieved, use the ContainableBehavior.

If you have conditions on some associated model that requires you to join other tables into the primary query, that's a different topic and there are many ways to go about it (search SO or ask a specific question). For that purpose manual joins may be appropriate. You do not need to manually join tables just to retrieve associated data though.

edited Dec 19 '21 at 09:40

Jean-François Fabre

137,073
23
153
219

answered Jul 02 '11 at 03:22

deceze

510,633
85
743
889

After many hours trying to figure this out using Cake's associations, I rephrased a previous question (using containable...etc) and [gave it a bounty here](http://stackoverflow.com/questions/6373203/select-all-events-with-event-schedule-date-between-start-and-end-dates-in-cakep). So far, the only answer that has the promise of working is using something similar to how I was doing it above. I tried and tried, but cannot get it to work without using manual joins..etc. Would LOVE to hear that I'm wrong because I'd rather use the associations I've set up. – Dave Jul 03 '11 at 15:06

Better way to retrieve HABTM, HasMany...etc data than -1 recursive manual joins in CakePHP?

1 Answers1