Column not found ERROR in my first CakePHP project

Question

I'm using CakePHP , and I'm getting the following Error ! Which is because

ON (`Trip`.`city_id` = `City`.`id`)

Should be

(`City'.`city_id` = `Trip`.`id`)

How Can i Correct that ?

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Trip.city_id' in 'on clause'

SELECT
    `Trip`.`id`, `Trip`.`user_id`, `Trip`.`type_id`, `Trip`.`title`,
    `Trip`.`city1`, `Trip`.`city2`, `Trip`.`date`, `Trip`.`free_places`,
    `Trip`.`description`, `Trip`.`contact_email`, `Trip`.`created`,
    `City`.`city_id`, `City`.`city_name`
FROM
    `mitfahr-ar`.`trips` AS `Trip`
LEFT JOIN
    `mitfahr-ar`.`cities` AS `City` ON (`Trip`.`city_id` = `City`.`id`)
WHERE 1 = 1

My Model code is :

<?php
class Trip extends AppModel
{
    public $name= 'Trip';
    public $belongsTo= array('City'); 
}
?>

Answer 1

[....] Which is because

ON (`Trip`.`city_id` = `City`.`id`)

Should be

(`City'.`city_id` = `Trip`.`id`)

No, it shouldn't, the latter one makes absolutely no sense, `City`.`city_id` would indicate a self-join, and in any case a single city could only be associated with a single other record, which makes no sense normalization wise.

The former expression generated by CakePHP is absolutely correct, in a belongsTo assocaition, the foreign key is held by the current model.

See http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#belongsto

So to fix the problem, add the proper city_id foreign key to your trips table.

Answer 2

ON (Trip.city_id = City.id)

Above query is because of the way you are associating the models.

Since in your below assocaiton:

<?php
class Trip extends AppModel
{
    public $name= 'Trip';
    public $belongsTo= array('City'); 
}
?>

you are not defining any foreign key hence according to the conventions of cakephp, it would automatically search for the field 'city_id' field and you have created that, hence the query.

Solution:

You have to define the foreign key attribute in your association.