Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Queryover where id is not in list

I have been struggling with this for a while, so I hope some of you QueryOver experts can help.

I have a list of blog posts. You can vote on each blog post, and I would like (among other) to recieve a list of posts where the user hasn't voted.

First I was thinking about doing something like:

Session.QueryOver<BlogPost>()
.WhereRestrictionOn(bp => bp.Id)
.NotIn(existingBlogPostVotes);

(existingBlogPostVoteIds is the ids of the voted blogposts)

But this doesn't exist in the QueryOver framework.

I found out that I could do it in Criteria like this:

var crit =
     Session.CreateCriteria<BlogPost>()
     .Add(Restrictions.Not(Restrictions.In("Id",existingBlogPostVotes)));

But I would to do this in QueryOver and not Criteria.

How would this be done in QueryOver?

like image 853
Dofs Avatar asked Feb 25 '12 13:02

Dofs


2 Answers

How about Not.IsIn():

   Session.QueryOver<BlogPost>()
          .WhereRestrictionOn(bp => bp.Id)
          .Not.IsIn(existingBlogPostVotes);

Optionally this could be done in the Linq provider as well:

   Session.Query<BlogPost>()
          .Where(bp => !existingBlogPostVotes.Contains(bp.Id));
like image 158
Cole W Avatar answered Oct 19 '22 03:10

Cole W


What about a standard query in which you use a sub query in the where clause. I don't have visual studio infront of me to verify the syntax, but essentially this is quering all Blog posts where there does not exist a blogPostVote record for the current user.

Session.QueryOver<BlogPost>()
.Where(bp => bp.Id)
.Where(
    !Session.QueryOver<BlogPostVotes>()
    .Where(blogPostVotes => blogPostVotes.BlogPostId == bp.Id)
    .Where(blogPostVotes => blogPostVotes.UserId == currentUserId)
    .Any());

That should give you the result your looking for. I know my syntax will work in Linq To Sql, if it doesn't work for NHibernate, look at this answer here (Subqueries with QueryOver)

like image 23
Zoidberg Avatar answered Oct 19 '22 03:10

Zoidberg