Raymond Camden's ColdFusion Blog: ColdFusion/ORM Example

ColdFusion/ORM Example - Filtering with a many to many

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):

view plain print about

<cfset r = ormExecuteQuery("from blogentry as be left join be.categories as cat where cat.id=4")>

1 <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.

This entry was posted on Saturday, November 7, 2009 at 12:08 PM. It was filed in the following categories: ColdFusion. It has been viewed 1,496 times and has 7 comments.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

Comment 1 written by Rick O on 7 November 2009, at 4:39 PM

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 ...)
Comment 2 written by Raymond Camden on 7 November 2009, at 5:02 PM

-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.
Comment 3 written by Rick O on 7 November 2009, at 5:45 PM

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".
Comment 4 written by Shannon Hicks on 9 November 2009, at 9:50 AM

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/group/cf-orm-dev/browse_t...

Shan
Comment 5 written by Raymond Camden on 9 November 2009, at 9:52 AM

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.
Comment 6 written by Kerr on 23 December 2009, at 7:40 AM

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. :)
Comment 7 written by Kerr on 23 December 2009, at 7:41 AM

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!