How to define Associations with model queries for multiple JOINs.

[cfcoder]

I have a table Attachments that has two join keys (that are not the PK) that reference into another table AttachmentCategory. But in that table it has both categories and subcategories. So Attachments.cat_id contains value of id from the Attachment_category.id column, and Attachments.subcat_id is the id that matches AttachmentCategory.id as well. The AttachmentCategory table has both categories and subcategories.

I tried making two associations in the Attachment.cfc model.

hasOne(name="AttachmentCat", modelName="AttachmentCategory", foreignKey="id", joinKey="cat_id");
hasOne(name="AttachmentSubCat", modelName="AttachmentCategory", foreignKey="id", joinKey="subcat_id");

The AttachmentCategory table has id, catType, category.

In my Attachments.cfc controller I added:

include="AttachmentCat",
include="AttachmentSubCat",

But it appears you can only do one include, the last one.

I also tried combining them in one include...
include="AttachmentCat, AttachmentSubCat",

But that throws an error:
The objects "AttachmentCategories" and "AttachmentCategories" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Any help would be appreciated.

[chapmandu]

You currently cannot join the same table twice because wheels doesn't distinguish between the same table in two different models. There reaches a point where one pushes the wheels ORM to its limit, and it becomes time to resort to some SQL.

It's a known limitation.
#1217

[dhgassoc]

Can you change your database schema?
If so, you may want to create a one to many relationship using an "Associative Entity" table.
I did this on one of my tables before and it worked for me. Requires a little extra coding, especially on view code.
To make my view code easier, I also did the following;

• create a category record with id=0, description="none".
• added validation code to make sure that for each Attachments record,
  I always validated to be sure to have both AttachmentCats types {cattype=0, and cattype=1},
  if no subcat, {defaulted to be id=0-none}

  
TABLE Attachments (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'primary key',
   ...
);

-- "Associative Entity" table
CREATE TABLE AttachmentCats (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `cattype` int DEFAULT 0 COMMENT 'Type of Category 0=cat, 1=subcat, optional: 9=No longer associated',
  `categoriyid` int DEFAULT 0 COMMENT 'Foreign key to categories'
);


CREATE TABLE categories (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'primary key',
   ...
);