+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
Comment preview