ORM Many To Many Relationship not working

Report · Feb 08, 2010

Hi there..

Am using ORM to link 3 tables together and am running into a few issues..

As it stands I have a table called Problem, and a table called Category. A problem may be placed under many categories, and a category will contain many problems. For this reason I have a link table called problemCategory which contains the foreign key to both tables, as so...

component output="false" table="tblProblemCategory" persistent="true"{
    property name="iPCRecId" fieldtype="id" generator="native";
    property name="iProblemId" ormtype="int";
    property name="iCategoryId" ormtype="int";

}

What I would like to do is grab all the problems under a certain category, so I have added the following code into my problem entity

property name="category" fieldtype="many-to-many" linktable="problemCategory" fkcolumn="iCategoryId" inversejoincolumn="iProblemId"cfc="category";

And this into my category entity.

property name="problem" fieldtype="many-to-many" linktable="problemCategory" fkcolumn="iProblemId" inversejoincolumn="iCategoryId" cfc="problem";

However, when I entity load my problems and do a cfdump, it shows category as an array, contained within every entry in the database, but the category array is always empty...

Would anyone have any ideas?

Thanks

Report · Feb 08, 2010

Ok, I seem to have found the solution..

I had actually created the class problemCategory myself...mapped to the table tblProblemCategory, which I was populating with the appropriate values, however it appears that orm creates the link table for you....

So when I add the following code into my problem entity

property name="category" fieldtype="many-to-many" linktable="problemCategory" fkcolumn="iCategoryId" inversejoincolumn="iProblemId"cfc="category";

And this into my category entity.

property name="problem" fieldtype="many-to-many" linktable="problemCategory" fkcolumn="iProblemId" inversejoincolumn="iCategoryId" cfc="problem";

ORM auto created the problemCategory table and class, in addition to the problemCategory class I had created myself..

Therefore I was populating the wrong table with values and the relationship was not working

Thanks