+972 52-548-6969
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
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 this_0_.Id AS y0_
FROM Users this_0_
INNER JOIN UsersBlogs blogs4_
ON this_0_.Id = blogs4_.UserId
INNER JOIN UsersBlogs users6_
ON blogs4_.BlogId = users6_.BlogId
INNER JOIN Users user2_
ON users6_.UserId = user2_.Id
WHERE this_0_.Id = @p0
Hopefully the query optimiser in the DB would figure this out though :)
Jon
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.
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?
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.
Comment preview