Geek asks and answers

How to use inner join in CakePHP models

I have two tables that I want INNER JOIN, I spent several hours, but I was out of luck. I will be very happy if someone can help.

My first table: properties

id | room | price | location_id

My second table: locations

id | country | province | district

Note: location_idto propertieshave 'id' inLocation

I want to use basic model associations, such as hasOne, belongsToetc.

What should I put in my models in order to get the following result?

SELECT
    Property.room,
    Property.price,
    Location.province
FROM
    properties AS Property
INNER JOIN locations AS Location ON Property.location_id = Location.id

Thanks in advance!

+3
source share
5 answers

The following model relation will create the requested query.

class Property extends AppModel {

    public $belongsTo = array(
        'Location' => array(
            'type' => 'INNER'
        )
    );

}

More on model associations

+3
source

Use the following code:

$this->Property->find('all', array('joins' => array(array('table' => 'locations',
                                   'alias' => 'Location',
                                   'type' => 'INNER',
                                   'conditions' => array('Property.LOCATION_ID = Location.ID')))));
+1

class Location extends AppModel {

    public $belongsTo = array('Property'));

}

class Property extends AppModel {

    public $hasOne = array( 'Location'));

}
0

:

class Variant extends AppModel {

    $this->bindModel(array('belongsTo' => array('Brand' => array('type' => 'INNER'))));

}
0

CakePHP supports syntax for defining your own connections. This will be done as follows:PropertiesController

$properties = $this->Property->find('all', array(
            'fields' => array('Property.*'),
            'joins' => array(
                array(
                        'table' => 'locations',
                        'alias' => 'Location',
                        'type' => 'INNER',
                        'conditions' => array(
                        'Property.location_id' =>'Location.id'
                        )
                    ),
                ),
            )
        );

Another way you can do it

You must create a relationship with your model and use constrained behavior, as shown below:

class Property extends AppModel {
    public $actsAs = array('Containable');
    public $belongsTo = array('Property');
}

class Location extends AppModel {
    public $actsAs = array('Containable');
    public $hasMany = array('Property');
}

Then you can do it from PropertiesController:

$properties = $this->Property->find('all', array(
    'contain' => array('Location')
));
0
source

More articles:


All Articles