Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add additional conditional to JOIN and not WHERE clause

Tags:

nhibernate

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
like image 377
Sean Lynch Avatar asked Jun 26 '12 20:06

Sean Lynch


People also ask

Should I put condition in join or WHERE clause?

It is better to add the condition in the Join. Performance is more important than readability. For large datasets, it matters.

Can we add a condition on join?

WHERE Conditions. The purpose of the ON clause is to specify the join conditions, that is, to define how the tables should be joined. Specifically, you define how the records should be matched.

Is it possible to join two tables without using WHERE clause?

Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION.

What is the difference between WHERE clause and join?

One difference is that the first option hides the intent by expressing the join condition in the where clause. The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query.


1 Answers

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)

like image 127
Sean Lynch Avatar answered Oct 13 '22 05:10

Sean Lynch