How to use inner join in CakePHP models

Question

I have two tables that I want to INNER JOIN, I spent hours but I had no luck. I will be very please if some could help.

My first table: properties

id | room | price | location_id

My second table is: locations

id | country | province | district

Note: location_id in Properties is 'id' in Location

I want to use basic model associations like hasOne, belongsTo and so on.

What should I put in my models so that I can basically 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!

Answer 1

The following model relation will generate the query you need.

class Property extends AppModel {

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

}

Read more about model associations