+972 52-548-6969
Querying collections in NHibernate
I got some interesting replies to my post about what not to do with ORM. I got several comments back, and the major one was how to do a query about an entity collections with using string concantation, which I said was a code smell.
Specifically, the most interesting challange was presented by Josh Robb, and it goes like this:
Blog
m:n Author
1:m Posts
n:m Categories
1:m Comments
Find all Posts where:
This being a part of a search screen where you can searc h by mulitply criteria, which are change dynamically. This is interesting because is spans just about any interesting assoication type that exists. So I set up to figure out how to do this.
My first step was to try to understand what I want NHibernate to do, so I started by writing the SQL for the above query:
SELECT
[post].[post_id],
[post].[post_blogid]
FROM
[dbo].[Posts] post
INNER JOIN [dbo].[Blogs] blog
ON post.post_blogid = blog.blog_id
INNER JOIN [dbo].[UsersBlogs] user2blog
ON blog.blog_id = user2blog.blog_id
INNER JOIN [dbo].[Users] author
ON author.user_id = user2blog.user_id
WHERE
author.user_name = 'josh' AND
EXISTS ( SELECT 1
[dbo].[Categories] category
INNER JOIN [dbo].[PostsCategories] post2category
ON post2category.category_id = category.category_id
WHERE
category.name = 'NHibernate' AND
post2category.post_id = post.post_id ) AND
EXISTS ( SELECT
[dbo].[Comments] comment
INNER JOIN [dbo].[Users] commenter
ON comment.user_id = commenter.user_id
WHERE
comment.comment_postid = post.post_id AND
commenter.user_name = 'Ayende' )a
Complex, isn't it? There may be better ways to do this, but that is the most straight forward way I could think of. Here is the query in HQL:
select post from Post post, User user
where user.UserName = 'Josh'
and user in elements(post.Blog.Users)
and exists (from Category category
where post in elements(category.Posts)
and category.Name = 'NHibernate')
and exists (from Comment comment
where comment.Post = post
and comment.Commenter.UserName = 'Ayende')
It generate nearly the same SQL that I wrote by hand, but uses tetha joins instead of ansi joins (I am not sure why, but the end result is the same).
Just a reminder, this isn't just a single query, this is something that that needs to be built dynamically, and we wish to handle this without using string concantation. In general, this means using the Criteria API. The first problem is that NHibernate's Criteria API doesn't allow you to query collections. This is very bad when you consider that this query is all about collections.
Strings are bad, I said so myself, so I put decided to add this to NHibernate (add is too strong a word, I went to Hibernate's source, found the relevant piece of code, and ported that). Now NHibernate can execute queries like section 15.8 in this page. All fine and dandy, until you get to the point where you need to query many-to-many assoications. You can't do that using the Criteria API in NHibernate (nor in Hibernate, as far as I can tell).
Many to many assoications are a funny beast, since you need to join through a second table to get the real value. I played a bit trying to implement support for that, but I wasn't successfull in the time frame that I could dedicate to it.
But, as the politician said, "This is a great question, but let me answer this one instead.", let us see what happens when we don't have many to many collections, shall we? The model is this, I change the assoication from Blogs to users to be a many to one, so a blog can now belong to a single author and remove categories.
Blog
m:1 Author
1:m Posts
1:m Comments
The query then becomes get all posts where:
That being the case, we can use the following code the create the query (again, SVN trunk only, I am afraid):
DetachedCriteria theComment = DetachedCriteria.For(typeof(Comment))
.SetProjection(Projections.Property("id"))
.Add(Expression.Property.ForName("Post.id").EqProperty("post.id"))
.CreateCriteria("User")
.Add(Expression.Expression.Eq("Name", "Ayende"));
IList list = s.CreateCriteria(typeof(Post), "post")
.Add(Expression.Expression.Like("PostTitle", "NHibernate",MatchMode.Anywhere))
.CreateCriteria("Blog.User")
.Add(Expression.Expression.Eq("Name", "Josh"))
.Add(Subqueries.Exists(theComment))
.List();
To conclude, at the moment NHibernate doesn't support queries over memebers of many to many collections using the criteria API (at the moment). But it does offer a good way to handle queries over memebers of collections in nearly all other cases.
So, allow me to retract my words a bit, if you have dynamic queries and you need to handle many-to-many assoication, HQL is your only choice. Nevertheless, I would strongly suggest to encapsulate this in an OO layer that would generate HQL under the cover, rather than build the HQL dynamically in the UI layer.
Comments
Comment preview