Let me be clear - I'm not sure this is the best way to do this - but it worked and as it took me a while to Google up the answer and test it, I thought I'd share.
Ok - so imagine an entity that has a many-to-many relationship. A good example is a blog entry with many categories. Any one blog entry may have multiple categories assigned to it. How would you write a query to return all blog entries in a specific category? Here is how I did it (and I apologize for not linking to the post - I forget exactly what I saw this):
<cfset r = ormExecuteQuery("from blogentry as be left join be.categories as cat where cat.id=4")>
Obviously the 4 there is hard coded and would be replaced with a bound parameter.
Archived Comments
Why not just put the inverse relationship in place on Categories so that you can say cat.BlogEntries ?
(I'm not trying to be argumentative -- I'm just curious. I always set up both sides of a relationship, but if there's some sort of weird performance implication to that or whatever ...)
-blush-
Yeah, you are right... although I think it's because of the example I gave. My real need was different and I thought this example was simpler. Let me share my real example, and you can see why that would not work... exactly. Although as I type I realize I might be wrong. ;)
Ugh. Nope... you are right. ;) I'll keep the HQL up as it was interesting - but I think I kinda screwed the pooch on this one.
Stop erring and being human ... it really freaks us out.
Seriously though, I just happened to be working on upgrading my ORM lecture for my students from CF8 to CF9, so I've been staring at it all day. Otherwise, I probably would have said "hey, neat trick".
I concur, creating the inverse relationship in Category is how I'd do it. If you need to sort/filter/pagninate those blog posts from inside your category, you can use this:
filter = ormGetSession().createFilter(Category.getBlogEntries(),"order by
this.blogPostTimeStamp desc").setFirstResult(0).setMaxResults(20);
postsArray = filter.list();
http://groups.google.com/gr...
Shan
One possible good use for my version though - since tags are text, this sql would allow me to use a generic search. If I did it via the inverse relationship, I'd first have to find matching tag objects based on names.
Well, Ray, I'm pretty green with CF ORM, and your post helped me to add m2m criteria to ORMExecute. I've been reading here and there about using Hibernate's criteria API via the ORM session, but I haven't had the opportunity to really get into it. I'm sure with time we'll see a lot more examples of the 'right' way to do this. :)
Oh, and the 'generic search' you mention in your last comment is my exact use case. I have been retrofitting a legacy app with ORM, and the 20 field search form is proving to be quite the challenge!
Thank you!
I have been searching for an example for a couple of days now.
I was trying to use the 'where' option on a 'many-to-many' property to filter out any items that have been marked as deleted. But the where clause for many-to-many properties actually queries the link table not the other object so I needed to create a custom function.
In any case, this is helpful!