CODEWITHSUNDEEP
sqlnhibernateinner-joinwhere-clause
Andriy Kizym
Andriy Kizym

Reputation: 1796

Nhibernate join filtering

I have a question about joins in NHIBERNATE. We had an issue with our sql query that was generated but nhibernate. Our db developer optimized the raw sql so it works as we need, but we need to change the nhibernate code to make generated sql look like optimized.

the part of the original part of the query is:

FROM   PERSON_VISIT this_
                     inner join PERSON_Basic per2_
                       on this_.PERSON_ID = per2_.PERSON_ID
                     left outer join PERSONC_QUESTIONS perint10_
                       on per2_.PERSON_ID = perint10_.PERSON_ID
                     left outer join TELEPHONE_QUESTIONS intaudit13_
                       on perint10_.PP_QUESTIONS_ID = intaudit13_.PP_QUESTIONS_ID
                     inner join C_QUESTIONS intdef14_
                       on perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and perint10_.QUESTIONS_CODE = intdef14_.QUESTIONS_CODE
                          and perint10_.QUESTION_ID = intdef14_.QUESTION_ID

The optimized one is :

FROM   PERSON_VISIT this_
                     inner join PERSON_Basic per2_
                       on this_.PERSON_ID = per2_.PERSON_ID
                     left outer join PERSONC_QUESTIONS perint10_
                       on per2_.PERSON_ID = perint10_.PERSON_ID
                     left outer join TELEPHONE_QUESTIONS intaudit13_
                       on perint10_.PP_QUESTIONS_ID = intaudit13_.PP_QUESTIONS_ID
                     left outer join C_QUESTIONS intdef14_
                       on perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and perint10_.QUESTIONS_CODE = intdef14_.QUESTIONS_CODE
                          and perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

To change query from inner join to left outer join is easy, i changed only one line of code:

        .CreateAlias("PersonInt.QuestionEntity", "IntDef", JoinType.LeftOuterJoin)

But how I can add 

                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

using nhibernate code?

There is an option to add reference from PERSON_VISIT definition to C_QUESTIONS, but the problem is that PERSON_VISIT is used everywhere and I don't want this change to possibly break other queries, I just wnat to add only one line of code to add, how I can do that? Is there any way to have access to the raw join to change it? Or some other way to add this 

                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

To the query? I know that somebody will say that we can add a restriction to the query through criteria.Add, but it is not an option cause db developer optimized our query taking this restriction from WHERE clause to the join.

How I can do that quickly without changing the models definitions? Just changing only this one query without changing the whole model?

Upvotes: 0

Views: 858

Answers (2)

Andriy Kizym
Andriy Kizym

Reputation: 1796

Thanks for answers. We use 2.0 version of NHibernate in our project so we didn't have a chance to use new methods of .CreateAlias with restrictions.

I have fixed an issue using Interceptors:

public class SqlInterceptor : EmptyInterceptor, IInterceptor
{
    SqlString IInterceptor.OnPrepareStatement(SqlString sql)
    {
        //manipulating with the sql

        return sql;
    }
}

than

        var factory = Session.SessionFactory;
        var session = factory.OpenSession(new SqlInterceptor());

And use my query without a change.

Upvotes: 0

Mark Perry
Mark Perry

Reputation: 1735

It is possible using HQL and the Criteria API's.

This question gives you the answer: Adding conditionals to outer joins with nhibernate

Something like this may solve your issue.

.CreateAlias("PersonInt.QuestionEntity", "IntDef", JoinType.LeftOuterJoin, 
      Restrictions.EqProperty("DISCIPLINE_CODE", "IntDef.DISCIPLINE_CODE"))

Upvotes: 2

Related Questions