I’m working on a few ORM entities in a client’s application tonight and came across an error that baffled me for a bit longer than it should have! I thought I would post it just in case someone else comes across it.

I have two entities: Teams and Sessions. A team can play in many sessions and a session can have many teams. They are joined via a link table in the DB. That table, SessionsTeams, has two columns: sessionid and teamid. It’s nothing more than a basic many-to-many relationship.

The problem was that every time I attempted to load the entities the application threw an error: invalid column name ‘teamid’. After some searching and failed attempts at fixing the issue via the fkcolumn and inversejoincolumn attributes, it hit me that those attributes were fine and I was, simply, assigning the wrong column name to the orderby attribute. Here’s where I went wrong:

CFC Entities

// Team.cfc component displayname="Team" alias="Team" output="false" accessors="true" persistent="true" entityname="Team" table="Teams"{  property name="ID" type="string" ormtype="string" column="id" generator="assigned" fieldtype="id"; property name="name" type="string" ormtype="string" column="name"; property name="sessions" type="array" fieldtype="many-to-many" CFC="Session" linktable="SessionsTeams" FKColumn="teamid" inversejoincolumn="sessionid" lazy="false" remotingFetch="true" cascade="all" orderby="team_id";    public Team function init(){ return this; } }    // Session.cfc component displayname="Session" alias="Session" output="false" accessors="true" persistent="true" entityname="Session" table="Sessions"{      property name="sessionID" type="numeric" ormtype="integer" column="id" generated="insert" generator="native" fieldtype="id"; property name="sessionTitle" type="string" ormtype="string" column="session_title"; property name="sessionTeams" type="array" fieldtype="many-to-many" CFC="Team" linktable="SessionsTeams" FKColumn="sessionid" inversejoincolumn="teamid" remotingFetch="true" lazy="false" cascade="all" orderby="id";           public Session function init(){ return this; } }


I mistakenly thought that the orderby attribute is meant to reference a column in the link table and not the source table. So, changing the orderby to reference the ‘id’ column/property in the source entity.

// In Team.cfc property name="sessions" type="array" fieldtype="many-to-many" ... orderby="id"; // In Session.cfc property name="sessionTeams" type="array" fieldtype="many-to-many" ... orderby="id";

When I was learning about creating the many-to-many relationship with CFML’s (Railo in my case) ORM, I never saw details in the docs about this attribute. So, I thought I’d post my screw-up story in the event someone else ends up down the path I took (and I hope no one does ;–).

Posted via email from self.is_a? Blog