Many-To-Many Relations in CFQL
CFQL actually provides built-in support for Many-To-Many relations, which often seems unnatural to developers. As a consequence, a common mistake is to write raw SQL to access a value contained in a Many-To-Many relation property, when in fact it can be done without any extra work.
For instance, in the following example a User can have several Roles, and one Role can be assigned to several Users. Nevertheless, if you want to load all users with a role name containing a specific token, instead of writing a raw SQL query, you could do it in a single line CFQL query, such as:
<Email typeName="Email" collectionKey="true"/>
<cf:method name="LoadByRoleToken" body="load(string token) where Roles.Name contains @token" />
<Name collectionKey="true" />
<Users typeName="UserCollection" />
As you can see in the example above, you can access entity properties using dots even on collection properties 🙂