HQL: Message Path expected for join!

Report · Feb 14, 2012

Hi,

I have 2 tables, "company" and "user" with a many-to-many relation:

<cfcomponent entityname="company" persistent="true" table="company" output="false">

<cfproperty name="user"

cfc="userGateway"

fieldtype="many-to-one"

fkcolumn="FK_user">

</cfcomponent>

<cfcomponent entityname="user" persistent="true" table="user" output="false">

<cfproperty name="companies"

cascade="all-delete-orphan"

fieldtype="one-to-many"

cfc="companyGateway"

fkcolumn="FK_user"

inverse="true"

singularname="company">

</cfcomponent>

When I want to retrieve a company based on the id of an associated user I do this:

<cfquery name="local.company" dbtype="hql">

select c.id

from company as c

join user as u

where u.id=<cfqueryparam value="#arguments.userId#">

</cfquery>

I add the join syntax _without_ the columns to join on since that is defined in the components using the cfproperty's fieldtype attribute.

<cfdump var="#local.company#"><cfabort> gives me this error:

Message Path expected for join! [ select c.id from company as c join user as u where u.id=? ]

QueryString select c.id from company as c join user as u where u.id=?

I guess either my hql is wrong or something is wrong with the mappings but I can't figure out what.. Does anyone have a clue?

Coldfusion 9,0,1,274733, Win7,MySQL 5.5.16

Marc

Report · Feb 14, 2012

Everything is ok except the query:

<cfquery name="local.company" dbtype="hql">

select c.id

from company as c

join user as u

where u.id=<cfqueryparam value="#arguments.userId#">

</cfquery>

should be

<cfquery name="local.company" dbtype="hql">

select c.id

from company as c

join c.user as u

where u.id=<cfqueryparam value="#arguments.userId#">

</cfquery>

Thanks,

Marc