Re: Struggling with Associations in 1.2.0.6311
Thanks Aaron, that's what I was missing! It seems a little counter- intuitive, but it works, which is what is most important. On Apr 21, 10:12 pm, aaron bauman <[EMAIL PROTECTED]> wrote: > Your HABTM relation is backwards. Course belongsTo Accreditation, and > Accreditation hasMany Course. > > The rule of thumb is that if a SQL table contains a foreignKey (as in > this case the courses table contains accreditation_id), then it should > implement a belongsTo relationship with the other table (in this case, > accreditations). Your business logic will determine which relationship > the other relationship (either hasOne or hasMany). > > /a > > On Apr 21, 11:49 am, NoelC <[EMAIL PROTECTED]> wrote: > > > I've come unstuck when attempting to establish associations between > > models. As far as I can tell, I've designed the database using Cake > > conventions, but the results I receive from finds on models with > > associations are incorrect. The Cake version is 1.2.0.6311 and the > > database is PostgreSQL 8.3. > > > These are the DDL statements for two of the tables in my app. > > > CREATE TABLE courses > > ( > > id bigint NOT NULL DEFAULT nextval('courses_id_seq'::regclass), > > course_code dm_short_code NOT NULL, > > course_title dm_title NOT NULL, > > course_details dm_description, > > accreditation_id integer NOT NULL, > > CONSTRAINT pk_courses PRIMARY KEY (course_id), > > CONSTRAINT fk_course_accreditations FOREIGN KEY (accreditation_id) > > REFERENCES accreditations (id) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE NO ACTION > > ) > > > CREATE TABLE accreditations > > ( > > id integer NOT NULL DEFAULT > > nextval('accreditation_id_seq'::regclass), > > accreditation_title dm_title NOT NULL, > > description dm_description, > > accreditor_id integer NOT NULL, > > CONSTRAINT pk_accreditations PRIMARY KEY (id) > > ) > > > The two related models are defined as: > > > class Course extends AppModel > > { > > var $name = 'Course'; > > > var $hasOne = array('Accreditation' => > > array('className' => 'Accreditation', > > 'foreignKey' => > > 'id' > > > ) > > ); > > > } > > > class Accreditation extends AppModel > > { > > > var $name = 'Accreditation'; > > var $belongsTo = array('Course' => > > array( 'className' => 'Course', > > 'foreignKey' => > > 'accreditation_id' > > ) > > ); > > > } > > > When doing a find on a Course, it seems to be joining the courses > > table to the accreditations table by using course.id = > > accreditation.id, which is not what I would expect from the model > > declarations above. > > > The generated SQL is: > > > SELECT "Course"."id" AS "Course__id", "Course"."course_code" AS > > "Course__course_code", "Course"."course_title" AS > > "Course__course_title", "Course"."course_details" AS > > "Course__course_details", "Course"."accreditation_id" AS > > "Course__accreditation_id", "Accreditation"."id" AS > > "Accreditation__id", "Accreditation"."accreditation_title" AS > > "Accreditation__accreditation_title", "Accreditation"."description" AS > > "Accreditation__description", "Accreditation"."accreditor_id" AS > > "Accreditation__accreditor_id" FROM "courses" AS "Course" LEFT JOIN > > "accreditations" AS "Accreditation" ON ("Accreditation"."id" = > > "Course"."id") WHERE "Course"."course_code" = 'X123' > > > Perhaps I am misunderstanding the way Cake works, but I have tried > > many different combinations (including setting $primaryKey in the > > Model) but to no avail. The last part of the SQL should read: > > > ON ("Accreditation"."id" = "Course"."accreditation_id") > > > This would give the expected results. > > > Can anyone give me any pointers as to what I may be doing wrong? > > > Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Struggling with Associations in 1.2.0.6311
Your HABTM relation is backwards. Course belongsTo Accreditation, and Accreditation hasMany Course. The rule of thumb is that if a SQL table contains a foreignKey (as in this case the courses table contains accreditation_id), then it should implement a belongsTo relationship with the other table (in this case, accreditations). Your business logic will determine which relationship the other relationship (either hasOne or hasMany). /a On Apr 21, 11:49 am, NoelC <[EMAIL PROTECTED]> wrote: > I've come unstuck when attempting to establish associations between > models. As far as I can tell, I've designed the database using Cake > conventions, but the results I receive from finds on models with > associations are incorrect. The Cake version is 1.2.0.6311 and the > database is PostgreSQL 8.3. > > These are the DDL statements for two of the tables in my app. > > CREATE TABLE courses > ( > id bigint NOT NULL DEFAULT nextval('courses_id_seq'::regclass), > course_code dm_short_code NOT NULL, > course_title dm_title NOT NULL, > course_details dm_description, > accreditation_id integer NOT NULL, > CONSTRAINT pk_courses PRIMARY KEY (course_id), > CONSTRAINT fk_course_accreditations FOREIGN KEY (accreditation_id) > REFERENCES accreditations (id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > > CREATE TABLE accreditations > ( > id integer NOT NULL DEFAULT > nextval('accreditation_id_seq'::regclass), > accreditation_title dm_title NOT NULL, > description dm_description, > accreditor_id integer NOT NULL, > CONSTRAINT pk_accreditations PRIMARY KEY (id) > ) > > The two related models are defined as: > > class Course extends AppModel > { > var $name = 'Course'; > > var $hasOne = array('Accreditation' => > array('className' => 'Accreditation', > 'foreignKey' => 'id' > > ) > ); > > } > > class Accreditation extends AppModel > { > > var $name = 'Accreditation'; > var $belongsTo = array('Course' => > array( 'className' => 'Course', > 'foreignKey' => > 'accreditation_id' > ) > ); > > } > > When doing a find on a Course, it seems to be joining the courses > table to the accreditations table by using course.id = > accreditation.id, which is not what I would expect from the model > declarations above. > > The generated SQL is: > > SELECT "Course"."id" AS "Course__id", "Course"."course_code" AS > "Course__course_code", "Course"."course_title" AS > "Course__course_title", "Course"."course_details" AS > "Course__course_details", "Course"."accreditation_id" AS > "Course__accreditation_id", "Accreditation"."id" AS > "Accreditation__id", "Accreditation"."accreditation_title" AS > "Accreditation__accreditation_title", "Accreditation"."description" AS > "Accreditation__description", "Accreditation"."accreditor_id" AS > "Accreditation__accreditor_id" FROM "courses" AS "Course" LEFT JOIN > "accreditations" AS "Accreditation" ON ("Accreditation"."id" = > "Course"."id") WHERE "Course"."course_code" = 'X123' > > Perhaps I am misunderstanding the way Cake works, but I have tried > many different combinations (including setting $primaryKey in the > Model) but to no avail. The last part of the SQL should read: > > ON ("Accreditation"."id" = "Course"."accreditation_id") > > This would give the expected results. > > Can anyone give me any pointers as to what I may be doing wrong? > > Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Struggling with Associations in 1.2.0.6311
Cake is doing exactly what you are telling it to do. Sadly, the Model::readMyMind() function has yet to be implemented. Take a look at your foreign keys in your model associations. It shouldn't take you more than a few minutes to realize what needs to be changed. -J. On Apr 21, 11:49 am, NoelC <[EMAIL PROTECTED]> wrote: > I've come unstuck when attempting to establish associations between > models. As far as I can tell, I've designed the database using Cake > conventions, but the results I receive from finds on models with > associations are incorrect. The Cake version is 1.2.0.6311 and the > database is PostgreSQL 8.3. > > These are the DDL statements for two of the tables in my app. > > CREATE TABLE courses > ( > id bigint NOT NULL DEFAULT nextval('courses_id_seq'::regclass), > course_code dm_short_code NOT NULL, > course_title dm_title NOT NULL, > course_details dm_description, > accreditation_id integer NOT NULL, > CONSTRAINT pk_courses PRIMARY KEY (course_id), > CONSTRAINT fk_course_accreditations FOREIGN KEY (accreditation_id) > REFERENCES accreditations (id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > > CREATE TABLE accreditations > ( > id integer NOT NULL DEFAULT > nextval('accreditation_id_seq'::regclass), > accreditation_title dm_title NOT NULL, > description dm_description, > accreditor_id integer NOT NULL, > CONSTRAINT pk_accreditations PRIMARY KEY (id) > ) > > The two related models are defined as: > > class Course extends AppModel > { > var $name = 'Course'; > > var $hasOne = array('Accreditation' => > array('className' => 'Accreditation', > 'foreignKey' => 'id' > > ) > ); > > } > > class Accreditation extends AppModel > { > > var $name = 'Accreditation'; > var $belongsTo = array('Course' => > array( 'className' => 'Course', > 'foreignKey' => > 'accreditation_id' > ) > ); > > } > > When doing a find on a Course, it seems to be joining the courses > table to the accreditations table by using course.id = > accreditation.id, which is not what I would expect from the model > declarations above. > > The generated SQL is: > > SELECT "Course"."id" AS "Course__id", "Course"."course_code" AS > "Course__course_code", "Course"."course_title" AS > "Course__course_title", "Course"."course_details" AS > "Course__course_details", "Course"."accreditation_id" AS > "Course__accreditation_id", "Accreditation"."id" AS > "Accreditation__id", "Accreditation"."accreditation_title" AS > "Accreditation__accreditation_title", "Accreditation"."description" AS > "Accreditation__description", "Accreditation"."accreditor_id" AS > "Accreditation__accreditor_id" FROM "courses" AS "Course" LEFT JOIN > "accreditations" AS "Accreditation" ON ("Accreditation"."id" = > "Course"."id") WHERE "Course"."course_code" = 'X123' > > Perhaps I am misunderstanding the way Cake works, but I have tried > many different combinations (including setting $primaryKey in the > Model) but to no avail. The last part of the SQL should read: > > ON ("Accreditation"."id" = "Course"."accreditation_id") > > This would give the expected results. > > Can anyone give me any pointers as to what I may be doing wrong? > > Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Struggling with Associations in 1.2.0.6311
I've come unstuck when attempting to establish associations between models. As far as I can tell, I've designed the database using Cake conventions, but the results I receive from finds on models with associations are incorrect. The Cake version is 1.2.0.6311 and the database is PostgreSQL 8.3. These are the DDL statements for two of the tables in my app. CREATE TABLE courses ( id bigint NOT NULL DEFAULT nextval('courses_id_seq'::regclass), course_code dm_short_code NOT NULL, course_title dm_title NOT NULL, course_details dm_description, accreditation_id integer NOT NULL, CONSTRAINT pk_courses PRIMARY KEY (course_id), CONSTRAINT fk_course_accreditations FOREIGN KEY (accreditation_id) REFERENCES accreditations (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) CREATE TABLE accreditations ( id integer NOT NULL DEFAULT nextval('accreditation_id_seq'::regclass), accreditation_title dm_title NOT NULL, description dm_description, accreditor_id integer NOT NULL, CONSTRAINT pk_accreditations PRIMARY KEY (id) ) The two related models are defined as: class Course extends AppModel { var $name = 'Course'; var $hasOne = array('Accreditation' => array('className' => 'Accreditation', 'foreignKey' => 'id' ) ); } class Accreditation extends AppModel { var $name = 'Accreditation'; var $belongsTo = array('Course' => array( 'className' => 'Course', 'foreignKey' => 'accreditation_id' ) ); } When doing a find on a Course, it seems to be joining the courses table to the accreditations table by using course.id = accreditation.id, which is not what I would expect from the model declarations above. The generated SQL is: SELECT "Course"."id" AS "Course__id", "Course"."course_code" AS "Course__course_code", "Course"."course_title" AS "Course__course_title", "Course"."course_details" AS "Course__course_details", "Course"."accreditation_id" AS "Course__accreditation_id", "Accreditation"."id" AS "Accreditation__id", "Accreditation"."accreditation_title" AS "Accreditation__accreditation_title", "Accreditation"."description" AS "Accreditation__description", "Accreditation"."accreditor_id" AS "Accreditation__accreditor_id" FROM "courses" AS "Course" LEFT JOIN "accreditations" AS "Accreditation" ON ("Accreditation"."id" = "Course"."id") WHERE "Course"."course_code" = 'X123' Perhaps I am misunderstanding the way Cake works, but I have tried many different combinations (including setting $primaryKey in the Model) but to no avail. The last part of the SQL should read: ON ("Accreditation"."id" = "Course"."accreditation_id") This would give the expected results. Can anyone give me any pointers as to what I may be doing wrong? Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---