Okay, first of all - don't think about the ORM stuff as tables. Think about it all as Objects. It makes it much easier.
Generally speaking, you don't (and in theory shouldn't) need to worry about the tables that store the data. That's up to Hibernate to manage. What it sounds like, is you are having an issue with joins in HQL. Took me a while to wrap my head around, until I realised I was thinking way too much like SQL. You have to think like objects. This is a bit pseduo code, so double check it. If you want to grab the users you want, there are few ways of doing the join: 1) from Users user where user.AccessGroupItem.AccessGroup = :name This is called an implicit join. By definition in the SQL it does an INNER JOIN 2) select user from Users user inner join user.AccessGroupItem accessgroup where accessgroup.AccessGroup = :name This is an explicit join, which you have a lot more control over. To learn more about joins, read here: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-joins As as side note - I would advocate the used of lowerCaseStartingPropertyNames, as HQL is case sensitive, and it tends to a be a widely followed convention regarding property names. Mark On Wed, Jul 14, 2010 at 11:23 AM, Steve Onnis <st...@cfcentral.com.au>wrote: > ok here is an example > > > *Users* UserID Username > > *UsersToAccessGroups* UserID AccessGroupID > > *AccessGroups* AccessGroupID AccessGroup > > In the AccessGroups table there is 4 values, *System Administrator*, *Network > Administrator*, *Guest and Member* > > Then i have a CFC for the *User* > > <cfcomponent persistent="true" table="Users" entityname="User"> > > <cfproperty name="UserID" type="numeric" column="UserID" fieldtype="id" > generator="increment" /> > <cfproperty name="UserName" type="string" column="UserName" > fieldtype="column" /> > > <cfproperty name="AccessGroupItem" > fieldtype="one-to-many" > type="array" > table="AccessGroups" > fkcolumn="UserID" > linktable="UsersToAccessGroups" > inversejoincolumn="AccessGroupID" > cfc="AccessGroups" > singularname="AccessGroup" /> > </cfcomponent> > > and i have a CFC for the *AccessGroup* > > <cfcomponent persistent="true" table="AccessGroups> > <cfproperty name="AccessGroupID" type="numeric" fieldtype="id" > column="AccessGroupID" /> > <cfproperty name="AccessGroup" type="string" fieldtype="column" > column="AccessGroup" /> > </cfcomponent> > > What i want to be able to do is say get all the users that belong to a > particular access group. I have tried using the *ORMExecuteQuery()*function > to specify a WHERE clause but it doesn't like anything i put in > there that has anything to do with the associated accessgroups object. I > have been through the help docs that talk about using queries for getting > data out of the orm 100 times ( > http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSf0ed2a6d7fb07688310730d81223d0356fc-7ffe.html) > and it shows that you can use the orm relationship for associated objects > but i didn't have any joy with it. > > Steve > > ------------------------------ > *From:* Mark Mandel [mailto:mark.man...@gmail.com] > *Sent:* Wednesday, 14 July 2010 7:46 AM > *To:* cfaussie@googlegroups.com > *Subject:* Re: [cfaussie] Filtering ORM results > > Steve I'm not sure what you are asking. > > What exactly do you want returned? > > How are you attempting to get the information? > > What sort of domain model are you attempting to implement here? > > If you are looking at filtering data globally, you may want to look at > Hibernate Filters: > http://docs.jboss.org/hibernate/core/3.3/reference/en/html/filters.html > > However, that can only be implemented through the .hbmxml files. > > Mark > > On Tue, Jul 13, 2010 at 10:23 PM, Steve Onnis <st...@cfcentral.com.au>wrote: > >> I have been playing around with the ORM stuff in CF9 and i was wondering >> if >> someone can answer something for me. Can you filter associated objects >> without doing it through the cfproperty where clause? >> >> The online docs say you have to do it like this.... >> <cfproperty name="unsoldArts" cfc="Art" fieldtype="one-to-many" >> fkcolumn="ARTISTID" where="issold=0"> >> >> I want to be able to do it like this ORMExecuteQuery("from art where >> issold >> = 0") but because the comes back as an array it doesn't work. >> >> Anyone shed some light on this? >> >> Steve >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "cfaussie" group. >> To post to this group, send email to cfaus...@googlegroups.com. >> To unsubscribe from this group, send email to >> cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com> >> . >> For more options, visit this group at >> http://groups.google.com/group/cfaussie?hl=en. >> >> > > > -- > E: mark.man...@gmail.com > T: http://www.twitter.com/neurotic > W: www.compoundtheory.com > > cf.Objective(ANZ) - Nov 18, 19 - Melbourne Australia > http://www.cfobjective.com.au > > Hands-on ColdFusion ORM Training > www.ColdFusionOrmTraining.com > > -- > You received this message because you are subscribed to the Google Groups > "cfaussie" group. > To post to this group, send email to cfaus...@googlegroups.com. > To unsubscribe from this group, send email to > cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/cfaussie?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "cfaussie" group. > To post to this group, send email to cfaus...@googlegroups.com. > To unsubscribe from this group, send email to > cfaussie+unsubscr...@googlegroups.com<cfaussie%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/cfaussie?hl=en. > -- E: mark.man...@gmail.com T: http://www.twitter.com/neurotic W: www.compoundtheory.com cf.Objective(ANZ) - Nov 18, 19 - Melbourne Australia http://www.cfobjective.com.au Hands-on ColdFusion ORM Training www.ColdFusionOrmTraining.com -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
