CODEWITHSUNDEEP
nhibernate
Sean Lynch
Sean Lynch

Reputation: 6487

Add additional conditional to JOIN and not WHERE clause

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

Upvotes: 5

Views: 4199

Answers (2)

Sean Lynch
Sean Lynch

Reputation: 6487

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)

Upvotes: 12

Diego Mijelshon
Diego Mijelshon

Reputation: 52725

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)

Upvotes: 0

Related Questions