CakePHP 3 - 1054 Column not found

Question

I have Two tables: cars and car_types. Cars "hasOne" type and Type "hasMany" cars. I've defined this constraints in my models but how can I read the values inside my controller or view without getting the error message like below?

Mysql error message:

"Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CarTypes.type_id' in 'on clause'"

I got this error when I do this inside my CarsController:

public function index() {
   $query = $this->Cars->find('all', ['contain' => [
        'CarTypes'
   ]]);

   debug($query->toArray());
}

Cars table:

id - type_id - method_id

Car_types table:

id - type

CarTypes model:

 public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('car_types');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->hasMany('Cars', [
            'foreignKey' => 'type_id'
        ]);
    }

Cars model:

public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('cars');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->hasOne('CarTypes', [
            'className' => 'Cars.CarTypes',
            'foreignKey' => 'type_id',
            'propertyName' => 'type'
        ]);
    }

Shadow Shadow · Accepted Answer · 2017-03-03T14:18:30

A record in the parent table (CarTypes) may have multiple child records (in Cars table), so that relationship is fine as hasMany. However, a child record (in the Cars table) should belong to one CarType, not have a CarType.

Essentially, has is the parent -> child relation, while belongs is the child -> parent relation (see cakephp documentation on associations for details). So, in the Cars change hasOne to belongsTo:

public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('cars');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsTo('CarTypes', [
            'className' => 'CarTypes',
            'foreignKey' => 'type_id',
            'propertyName' => 'type'
        ]);
    }

In this case CakePHP will look for the type_id field in the Cars table, not in the CarTypes table.

CakePHP 3 - 1054 Column not found

1 Answers