Search code examples
coldfusionrelationshipcfccfwheels

many to many relationship cfwheels inner join

i have a couple of problems with cfwheels (n:m relationsship)

here my database schema

rights        righthaspath           path        
-id -------|  -id                --> -id
-role      -->-rightid (FK)      |   -url
              -pathid  (FK)------|

my models /Models/Right.cfc

<cffunction name="init">
    <cfset hasMany(name="righthaspath",shortcut="path")>
    <cfset nestedProperties(associations="righthaspath")>   
</cffunction>

/Models/Path.cfc

<cffunction name="init">
    <cfset hasMany(name="righthaspath")>
</cffunction>

/Models/Righthaspath.cfc

<cffunction name="init">
    <cfset belongsTo("path")>
    <cfset belongsTo("right")>
</cffunction>

in my Controller

<cfset tmp= model("right").findall(include="righthaspath")>

so, the sql statement from cfhweels is:

SELECT rights.id,rights.Role,righthaspaths.id AS righthaspathid,righthaspaths.pathID,righthaspaths.rightID FROM rights 
LEFT OUTER JOIN righthaspaths ON rights.id = righthaspaths.rightID

but i want a sql statement over the three tables like this

SELECT 
    *
FROM 
    rights 
    INNER JOIN righthaspaths on rights.id=righthaspaths.rightID
    INNER JOIN paths on righthaspaths.pathID=paths.id

can u help?

PS: i cant include path e.g.

<cfset tmp= model("right").findall(include="righthaspath,path")>

because i got an error

An association named path could not be found on the right model.
Solution

  • On behalf of Hans Maeier above, I'll post his answer so it can be marked as answered and aid the CFWheels community on stackoverflow.

    include="righthaspath(path)" will work done

    Hope that helps anyone searching for this.

    Thanks, Michael.