Formatting CakePHP join statement

Question

I'm using CakePHP 2.3.8 and I'm trying to more efficiently join two tables. buildings and building_rental_rates

buildings
id  |   name  |   description    |  property_owner  |  building_type
 1      Big      Big Building               1               1

building_rental_rates
id  |   building_type |   rate_name    |      rate
 1            1              daily          150.00
 2            1              hourly         15.00

I want to look up a building and select the different rental rates. The tables are joined on building_type. Here's the find statement I have

$buildings = $this->Building ->find('all',array(
                        'Building.property_owner' => '1',
                        'fields' => array('Building.*','BuildingRentalRate.*'),
                        'joins' => array(
                            array(
                                'table' => 'building_rental_rates',
                                'alias' => 'BuildingRentalRate',
                                'type' => 'inner',
                                'conditions' => array(
                                    'Building.building_type = BuildingRentalRate.building_type'
                                )
                            )
                        )
                    ));

Here is the result

Array
(
    [0] => Array
        (
            [Building] => Array
                (
                    [id] => 1
                    [name] => Big
                    [description] =>  Big Building
                    [property_owner] => 1
                    [building_type] => 1
                )

            [BuildingRentalRate] => Array
                (
                    [id] => 1
                    [building_type] => 1
                    [rate_name] => daily
                    [rate] => 150.00
                )

        )

    [1] => Array
        (
            [Building] => Array
                (
                    [id] => 1
                    [name] => Big
                    [description] =>  Big Building
                    [property_owner] => 1
                    [building_type] => 1
                )

             [BuildingRentalRate] => Array
                (
                    [id] => 2
                    [building_type] => 1
                    [rate_name] => hourly
                    [rate] => 15.00
                )

        )
)

While the data is found properly, it's a pain to traverse through it. Can I use a join statement to result in this output? Notice how the BuildingRentalRate is an array containing all entries of that table that share the same building_type

Array
(
    [0] => Array
        (
            [Building] => Array
                (
                    [id] => 1
                    [name] => Big
                    [description] =>  Big Building
                    [property_owner] => 1
                    [building_type] => 1
                )

            [BuildingRentalRate] => Array
                (
                  [0] => Array
                     (
                        [id] => 1
                        [building_type] => 1
                        [rate_name] => daily
                        [rate] => 150.00
                     )
                  [1] => Array
                     (
                        [id] => 2
                        [building_type] => 1
                        [rate_name] => hourly
                        [rate] => 15.00
                      )
                )

        )
)

I know Cake can output results like this when using model associations, but I wasn't able to get my associations correct apparently because it kept on joining Building.id on BuildingRentalRate.building_type (it should be Building.building_type = BuildingRentalRate.building_type)

Even with my association like this...

 //Building.php
 public $hasMany = array('BuildingRentalRate' => array('foreignKey' => 'building_type'));

 //BuildingRentalRate.php
 public $belongsTo = array('Building' => array('foreignKey' => 'building_type'));

It would join Building.id on BuildingRentalRate.building_type despite specificying building_type in both models as the foreign key.

Can I do some kind of nested SQL query in the conditions or is there an easier way of doing this?

arilia arilia · Accepted Answer · 2014-05-15T07:29:31

you should be able to avoid using joins if in BuildingRentalRate.php you set your relationship this way

$public belongsTo = array(
    'Building' => array(
        'foreignKey' => false,
        'conditions' => array
        (
            'Building.building_type' => 'BuildingRentalRate.building_type'
        )
    )
);

Formatting CakePHP join statement

1 Answers