TQL Many-To-Many Bidirectional Relationship

[Robert Rawlins]

Hello Guys,

I have a couple of objects, for arguments sake let's call them Foo and Bar.

I have a relationship defined that says Foo has many Bars.

Now, Transfer gives me a bunch of generated methods on Foo to get an array of Bars, add Bars, remove Bars etc.

However, say I have a Bar object and I want to use TQL to get all the associated Foo's

How could I do that? I've used TQL a bit with different joins, but never a M2M

Robert

[Pedro Bezunartea López]

Hi Robert,

I think that's well explained in the documentation: http://docs.transfer-orm.com/wiki/Managing_Relationships_and_Compositions.cfm

The short answer is that having established a OneToMany relation, Foo to Bars, you can use the method Bar.getParentFoo().

HTH,

Pedro

[Robert Rawlins]

Hi Pedro,

Thanks. On re-reading my question I realize I wasn't very clear. What I have is a many-to-many relationship. Defined on the Foo object.

Transfer generates methods on Foo for getting and changing the collection of bars.

However, I want some TQL so given a specific Bar object, I can access all it's related Foo's.

Thanks.

Robert

[Pedro Bezunartea López]

Hi Robert,

It should not be too difficult, I've added all kind of methods to my decorators. This is how I'd do it:

1. The model persists its data in 3 tables for the 2 objects and their many to many relationship (foos, bars and lnk_FooBar). Transfer.xml should look something like:

<object name="Foo" table="foos" >
 <id name="FooId" column="FOO_ID" type="GUID" generate="true" />
 <property name="Name" type="string" column="name" />
...
 <manytomany name="Bars" table="lnk_FooBar">
  <link to="...Foo" column="lnkIDFoo"/>
  <link to="...Bar" column="lnkIDBar"/>
  <collection type="array"/>
 </manytomany>
</object>

<object name="Bar" table="bars" decorator="...decorators.Bar">
 <id name="BarId" column="BAR_ID" type="GUID" generate="true" />
 <property name="Name" type="string" column="name" />
...
</object>

The table lnkFooBar should only consist of 2 columns, lnkIDFoo and lnkIDBar.

2. In the decorator for Bar, I'd add the following method:

<cffunction name="getParentFoos" access="public" returntype="Array">
 <cfset var foos = ArrayNew(1) />
 <cfset var qry = getTransfer().createQuery("FROM ...Foo JOIN ...Bar WHERE ...Bar.BarId = :myId") />
 <cfset var foosQuery = "" />

 <cfset qry.setParam("myId", getBarId()) />
 <cfset foosQuery = getTransfer().listByQuery(qry) />
 <cfloop query="foosQuery">
  <cfset ArrayAppend(foos, getTransfer().get("...Foo", foosQuery.FooId) ) />
 </cfloop>
 <cfreturn foos />
</cffunction>

Every "..." should be replaced with the package name. A similar method could obtain structs.

HTH,

Pedro.