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

Wednesday, November 4, 2015

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

0 comments:

Post a Comment