Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,955 | Comments: 44,412

filter by tags archive

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

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

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

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

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

Comments have been closed on this topic.

FUTURE POSTS

  1. What is new in RavenDB 3.5–Intro - 2 hours from now
  2. Production postmortem: The case of the infected cluster - about one day from now

There are posts all the way to Jul 09, 2015

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats