Trying to get "City" data from Event->Venue->City using 'contain' in CakePHP

Question

I'm trying to return a list of events, and include the city where it's taking place. The city is only associated through the Event's Venue though.

Below is the code I'm using. It returns all the correct data, but it doesn't return ANY city data (other than the city_id field in Venue - which I'm not sure why it's returning).

Associations:

Event belongsTo Venue
Venue hasMany Event

Venue belongsTo City    
City hasMany Venue

Code:

        $this->Event->Behaviors->attach('Containable');
        $events = $this->Event->find('all', array(
            'limit' => 5,
            'order' => 'Event.created DESC',
            'fields' => array(
                'name',
                'description',
                'phone',
                'price_general',
                'price_child',
                'price_adult',
                'price_child',
                'tickets_url'
            ),
            'contain' => array(
                'Venue' => array(
                    'fields' => array(
                        'name',
                        'address',
                        'city_id',
                    ),
                    'City' => array(
                        'fields' => array(
                            'City.name',
                            'state'
                        ),
                        'conditions' => array(
                            'City.id' => 'Venue.city_id'
                        )
                    )
                ),
                'Schedule' => array(
                    'fields'=>array(),
                    'Date' => array(
                        'conditions'=>array(
                            'Date.start >=' => $start_date,
                            'Date.start <=' => $end_date,
                        )
                    )
                )
            ),
        ));

Bonus answer: (that I have currently asked in another StackOverflow question) - The Date conditions are supposed to filter which events show up, but instead, they're only filtering which Date data to show.

WORKING ANSWER: (thanks bancer)

    $this->Event->recursive = -1;
    $options['joins'] = array(
            array('table' => 'schedules',
                'alias' => 'Schedule',
                'type' => 'LEFT',
                'conditions' => array(
                    'Event.id = Schedule.event_id',
                )
            ),
            array('table' => 'dates',
                'alias' => 'Date',
                'type' => 'LEFT',
                'conditions' => array(
                    'Date.schedule_id = Schedule.id',
                )
            )
        );
        $options['fields'] = array(
            'Event.name',
            'Schedule.start_date',
            'Date.start',
        );
        $options['limit'] = 5;
        $events = $this->Event->find('all', $options);

It would help if you provide the query that is produced by your code. — bancer, Jun 16 '11 at 22:25
What is the actual query produced by your code? It seems that you have an error in the Date 'conditions'. It should be `'Date.schedule_id' => 'Schedule.id'`. Comment out 'fields' and 'limit' arrays temporary until you ensure that you can retrieve all columns and rows. — bancer, Jun 17 '11 at 09:23
Also take a look at this manual to better understand difference between left and right joins http://w3schools.com/sql/sql_join.asp — bancer, Jun 17 '11 at 09:29
@bancer - so - if I understand it right (been awhile for me to do joins), I should always be using Left Join when Event is my main query, right? — Dave, Jun 17 '11 at 13:40
I think in most cases you will need left joins if I understand your schema correctly. — bancer, Jun 17 '11 at 17:18
@bancer - The problem so far is - if I put "LEFT" as the date join, it doesn't show the Date data. If I put RIGHT, then it does, but doesn't show the Event or Schedule data. — Dave, Jun 17 '11 at 17:51
NOW we're getting somewhere - SQL posted. When I change 'Date.id' to `Date`.`id` manually (navicat), it returns all the data. Now, how do I get Cake to do it? — Dave, Jun 17 '11 at 18:34
I am going to repeat myself but it seems you have an error in Date 'conditions'. It should be `'Date.schedule_id' => 'Schedule.id'`. Actually, better would be `'Schedule.id' => 'Date.schedule_id'` — bancer, Jun 17 '11 at 18:37
AHA!!! (First off, sorry for missing your previous comment). You were right - ALSO - I needed to change ' => ' to just = within quotes (like the one above it. Working now!!! Thank you VERY much! — Dave, Jun 17 '11 at 18:43

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

I would recommend to avoid using Containable. It generates too many queries in some cases. A better way for complex queries is to join tables "manually".

Another option I would consider at the first place is to search through 'Venue' model without using Containable like this: $this->Event->Venues->find('all', ...). As Venues directly associated with Cities and Events there should be possible to get what you want without extra complexities.

Update: take a look at the question How to change the sequence of 'joins' in CakePHP?

edited May 23 '17 at 11:55

Community

1
1

answered Jun 16 '11 at 22:21

bancer

7,475
7
39
58

I'd like to get this way functioning before I try to go down another endless road of not understanding. I understand what you mean, and will look into doing it "manually" later, but - for now, I just want to get it working, slow or otherwise. – Dave Jun 17 '11 at 01:16
Also - I need to get more things than Events, Venues, and Cities - like Schedules, which is only linked via Events – Dave Jun 17 '11 at 05:19
So I think I give up - how do I do this manual join? I've read the link you provided "join tables manually", but I've tried it, and it doesn't work (or I'm doing something wrong). Don't suppose you can point me in the right direction on how to translate their code to this example? – Dave Jun 17 '11 at 05:21
Code I wrote that worked (based on this answer) is listed above in the question. – Dave Jun 17 '11 at 18:45

score 0 · Answer 2 · answered Jun 16 '11 at 21:32

0

instead of containable, did you try including the city data in fields itself by fields=> array('','','City.name)

answered Jun 16 '11 at 21:32

Gaurav Toshniwal

3,552
2
24
23

Since an Event is not associated directly with a City, the only place I can think to add it (other than the place it already is) is under Venue - and that returns: SQL Error: 1054: Unknown column 'City.name – Dave Jun 16 '11 at 21:35

Trying to get "City" data from Event->Venue->City using 'contain' in CakePHP

2 Answers2