Add additional conditional to JOIN and not WHERE clause

Question

I've been trying to accomplish this for a few days using QueryOver without much progress. I can't seem to find a way to add a conditional on a left outer join on a one to many relationship. I have Question and Answer entities, where a Question has multiple Answers (it's for a Survey, where each response is another Answer to the same Question). I'm trying to filter all the Answers based on some criteria (for example, all answers with a Score < 3), but anytime I attempt to add the conditional, it gets added to the WHERE clause and not on the JOIN.

Example entities

Question:

public class Question : Entity<int>
{
    public virtual IEnumerable<Answer> Answers { get; set; }
    ...
}

Answer:

public class Answer : Entity<int>
{
    public virtual Question Question { get; set; }
    public virtual int Score { get; set; }
    ...
}

Queries

I've tried many different variants of using JoinQueryOver...

session.QueryOver<Question>()
    .Where(q => q.Survey.Id == id)
    .Left.JoinQueryOver(q => q.Answers)
    .Where(a => a.Score < 3)

...and JoinAlias and using the alias in the Where

session.QueryOver<Question>(() => questionAlias)
    .Where(q => q.Survey.Id == id)
    .Left.JoinAlias(() => questionAlias.Answers, () => answerAlias)
    .Where(() => answerAlias.Score > 3);

I always get a query like this:

SELECT * FROM QUESTION q
left outer join ANSWER a on q.Id=a.Question_id
WHERE q.Survey_id = 1 and a.Score < 3

but I need:

SELECT * FROM QUESTION q
left outer join ANSWER a on q.Id=a.Question_id and a.Score < 3
WHERE q.Survey_id = 1

score 11 · Accepted Answer · edited May 23 '17 at 12:14

11

Turns out I was using an older version of NHibernate (3.1) that didn't support this, but upgrading to 3.3 now has an additional withClause you can pass, like so:

Answer answerAlias = null;
var questionQuery = session.QueryOver<Question>()
    .Where(q => q.Survey.Id == id)
    .Left.JoinQueryOver(q => q.Answers, () => answerAlias, a => a.Score > 3);

I believe this is supported as of 3.2 (the accepted answer on this question is what tipped me off)

edited May 23 '17 at 12:14

Community

1
1

answered Jun 27 '12 at 13:42

Sean Lynch

6,267
2
43
45

1

I wish I could favourite answers. This helped me a lot, thanks! – iamserious Jun 26 '13 at 11:42
So glad I found this answer, exactly what I have been looking for, thanks a lot – Chris Knight Sep 24 '13 at 18:30
For the love of all that is right in the world! Thank you! I soooo wish nhibernate documentation was better. – mcfea Feb 11 '15 at 19:07

score 0 · Answer 2 · answered Jun 27 '12 at 02:13

0

IIRC, the only query API that supports this is HQL:

from Question q
left join q.Answers a with a.Score < 3
where q.Survey.Id = :id

(untested)

answered Jun 27 '12 at 02:13

Diego Mijelshon

52,548
16
116
154

Hmm, I'm trying to filter the Answer collection each Question, but still return Questions. Is there another way to do this maybe using a subquery? In other words, I don't want question.Answers to lazy load all Answers, but a filters subset of them. – Sean Lynch Jun 27 '12 at 13:06
You can't return an incomplete collection as part of an entity. Use a projection instead, or load the Question first, and then use `session.Filter(question.Answers, "where Score < 3")` to get a filtered subset of answers. – Diego Mijelshon Jun 27 '12 at 15:53

Add additional conditional to JOIN and not WHERE clause

Example entities

Queries

2 Answers2