ColdFusion/ORM Example - Filtering with a many to many

This post is more than 2 years old.

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Rick O posted on 11/8/2009 at 3:39 AM

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 by Raymond Camden posted on 11/8/2009 at 4:02 AM

-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 by Rick O posted on 11/8/2009 at 4:45 AM

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 by Shannon Hicks posted on 11/9/2009 at 8:50 PM

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

Comment 5 by Raymond Camden posted on 11/9/2009 at 8:52 PM

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 by Kerr posted on 12/23/2009 at 6:40 PM

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 by Kerr posted on 12/23/2009 at 6:41 PM

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!

Comment 8 by Laura Hansen posted on 8/3/2012 at 5:24 AM

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!