Coldfusion orm oneToMany join query don't work
I have 2 persistent entities : Product & ProductMaterial.
Product can't be sumarize like This :
And ProductMaterial is :
I want to get the products according a selected material, for instance 'Wood'
I have tried, among other things similar, this :
But it returns me 0 records (there must be 433 records !).
Do u have an idea what's wrong ?
Thanks.
Product can't be sumarize like This :
<cfproperty name="id" type="numeric" fieldtype="id" ormtype="int" generator="native" setter="false" hint="the unique id of the product">
...
<cfproperty name="materials" type="array" fieldtype="one-to-many" hint="the materials of the product" cfc="ProductMaterial" singularname="material" fkcolumn="productId" orderby="name" cascade="all-delete-orphan">And ProductMaterial is :
<cfproperty name="id" type="numeric" fieldtype="id" ormtype="int" generator="native" setter="false" hint="the unique id of the material">
<cfproperty name="name" type="string" fieldtype="column" ormtype="string" length="50" validate="string" validateparams="{minLength=1,maxLength=50}" hint="the name of the material">I want to get the products according a selected material, for instance 'Wood'
I have tried, among other things similar, this :
<cfset variables.products = ormExecuteQuery("from Product p join p.materials m where m.name = 'wood' and isPack=true order by referenceId desc")>But it returns me 0 records (there must be 433 records !).
Do u have an idea what's wrong ?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have receive no corect answer, I have found myself the solution (as described in the comment).
from Product p join p.materials m where m.name
should either be
from Product p, materials m
Where p.col=m.com and m.name =
or
from Product p
LEFT JOIN Materials m
on p.col=m.col
WHERE m.name=