Ayende @ Rahien

Refunds available at head office

NHibernate Queries: Find all users that are members of the same blogs as this user

Let us assume that we have the following model:

  • User
    • n:m -> Blogs
      • n:m -> Users

Given a user, how would you find all the users that are members of all the blogs that the user is a member of?

Turn out that NHibernate makes it very easy:

DetachedCriteria usersForSameBlog = DetachedCriteria.For<User>()
	.Add(Expression.IdEq(userId))
	.CreateCriteria("Blogs")
	.CreateCriteria("Users", "user")
	.SetProjection(Projections.Id());
session.CreateCriteria(typeof(User))
	.Add(Subqueries.PropertyIn("id", usersForSameBlog))
	.List();

And the resulting SQL is:

SELECT this_.Id        AS Id5_0_,

       this_.Password  AS Password5_0_,

       this_.Username  AS Username5_0_,

       this_.Email     AS Email5_0_,

       this_.CreatedAt AS CreatedAt5_0_,

       this_.Bio       AS Bio5_0_

FROM   Users this_

WHERE  this_.Id IN (SELECT this_0_.Id AS y0_

                    FROM   Users this_0_

                           INNER JOIN UsersBlogs blogs4_

                             ON this_0_.Id = blogs4_.UserId

                           INNER JOIN Blogs blog1_

                             ON blogs4_.BlogId = blog1_.Id

                           INNER JOIN UsersBlogs users6_

                             ON blog1_.Id = users6_.BlogId

                           INNER JOIN Users user2_

                             ON users6_.UserId = user2_.Id

                    WHERE  this_0_.Id = @p0)

Comments

Jon Skeet
01/24/2008 02:58 PM by
Jon Skeet

I'm probably being stupid, but doesn't that SQL find the users that are members of any of the same blogs that the specified user is a member of? (Rather than all, as specified in the post.)

And out of interest, am I right in saying we don't actually need the join to Blogs at all? Assuming there are appropriate constraints, the subselect could be:

SELECT this0.Id AS y0_

FROM Users this0

INNER JOIN UsersBlogs blogs4_

ON this0.Id = blogs4_.UserId

INNER JOIN UsersBlogs users6_

ON blogs4.BlogId = users6.BlogId

INNER JOIN Users user2_

ON users6.UserId = user2.Id

WHERE this0.Id = @p0

Hopefully the query optimiser in the DB would figure this out though :)

Jon

Ayende Rahien
01/24/2008 03:56 PM by
Ayende Rahien

Jon,

Yes, you are correct, I wasn't precise enough.

As for skipping the blog's join, it would be valid, but it is likely that using the fully path will allow the DB to utilize the FK indexes.

Craig Neuwirt
01/25/2008 03:26 PM by
Craig Neuwirt

Maybe I am reading it wrong, but shouldn't the projection be something like

.SetProjection(Property.ForName("user.id")) so we get the related user ids?

Ayende Rahien
01/25/2008 03:31 PM by
Ayende Rahien

No, create criteria means that you are now in a sub criteria, so the entity you now refer to is the user.

If I would have used the CreateAlias(), then I would have to do so.

"user.id" would work as well, btw.

Comments have been closed on this topic.