[FIXED] CakePHP generates SQL statement with error for two linked tables. Not Unique Alias

Issue

I have two tables linked by a foreign key - college_id The code for it was all generated using the cake bake feature.

Whenever I try to call the view generated by CakePHP for one of the tables it always generates a SQL statement with error.

The error is:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Courses'

SQL Statement:

SELECT Courses.course_id AS `Courses__course_id`, Courses.course_name AS `Courses__course_name`, Courses.cource_code AS `Courses__cource_code`, Courses.college_id AS `Courses__college_id`, Colleges.college_id AS `Colleges__college_id`, Colleges.college_name AS `Colleges__college_name`
FROM courses Courses
    INNER JOIN courses Courses ON Courses.course_id = (Courses.course_id)
    INNER JOIN colleges Colleges ON Colleges.college_id = (Courses.college_id)
LIMIT 20 OFFSET 0

I know that the error is in the fact that is using twice the alias "Courses" in the same query. Here: "FROM courses Courses INNER JOIN courses Courses"

But I can't find a way to prevent this from happening. I looked into the Linking tables docs for cakephp 3.0 but I couldn't find a way where I could define the way it names this alias. Any hints?

CoursesTable.php

<?php
namespace App\Model\Table;

use App\Model\Entity\Course;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;

/**
 * Courses Model
 *
 * @property \Cake\ORM\Association\BelongsTo $Courses
 * @property \Cake\ORM\Association\BelongsTo $Colleges
 */
class CoursesTable extends Table
{

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('courses');
        $this->displayField('course_name');
        $this->primaryKey('course_id');

        $this->belongsTo('Courses', [
            'foreignKey' => 'course_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsTo('Colleges', [
            'foreignKey' => 'college_id',
            'joinType' => 'INNER'
        ]);
    }

    /**
     * Default validation rules.
     *
     * @param \Cake\Validation\Validator $validator Validator instance.
     * @return \Cake\Validation\Validator
     */
    public function validationDefault(Validator $validator)
    {
        $validator
            ->requirePresence('course_name', 'create')
            ->notEmpty('course_name')
            ->add('course_name', 'unique', ['rule' => 'validateUnique', 'provider' => 'table']);

        $validator
            ->requirePresence('cource_code', 'create')
            ->notEmpty('cource_code');

        return $validator;
    }

    /**
     * Returns a rules checker object that will be used for validating
     * application integrity.
     *
     * @param \Cake\ORM\RulesChecker $rules The rules object to be modified.
     * @return \Cake\ORM\RulesChecker
     */
    public function buildRules(RulesChecker $rules)
    {
        $rules->add($rules->existsIn(['course_id'], 'Courses'));
        $rules->add($rules->existsIn(['college_id'], 'Colleges'));
        return $rules;
    }
}

Course Table Schema:

course_id - PK
course_name
course_dode
college_id - FK

Solution

Looking at your schema for the courses table the issue would seem to be that you have called your primary key course_id rather than the expected id. Cake bake will interpret this as a foreign key to the courses table (i.e. itself) so create a belongsTo relationship. You should rename the column id and stick with CakePHP's naming conventions.

Diverging from the naming conventions will almost always cause you more trouble than it's worth. bake cannot be expected to know how to build your app correctly if you are not conforming to the standards.

There are few cases where not using the conventions are justified.

Answered By - drmonkeyninja