From Clause Criteria?

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

Do you know how to produce this sql with ICriteria? I'm interested in adding extra criteria to the from clause (not in the where clause). Please note this line in the following two queries "this_.SiteProductID <> siteproduc2_.SiteProductID"

I need this:

Code:

select this_.*
from SiteProduct this_

left outer join SiteProduct siteproduc2_
on   this_.ProductID = siteproduc2_.ProductID
and           this_.SiteProductID <> siteproduc2_.SiteProductID
and   siteproduc2_.DeletedOn IS NULL

where this_.DeletedOn is null and this_.SiteID = 119 and this_.IsProduced = 0 and (siteproduc2_.IsProduced = 1 or siteproduc2_.IsProduced is null)

V.S.

I have this:

Code:

select this_.*
from SiteProduct this_

left outer join SiteProduct siteproduc2_
on   this_.ProductID = siteproduc2_.ProductID
and   siteproduc2_.DeletedOn IS NULL

where this_.DeletedOn is null and this_.SiteID = 119 and this_.IsProduced = 0 and (siteproduc2_.IsProduced = 1 or siteproduc2_.IsProduced is null)
and           this_.SiteProductID <> siteproduc2_.SiteProductID

zafarjcp · **Joined:** Sun May 18, 2008 1:21 pm **Posts:** 13

First give your mapping information.

2nd mean while try these 3 things

1. create a self join in database
2. in mapping file add a relation to itself.
3. while using ICriteria use Expression.Add(first condition Eq).Add(2nd Condition Eq)

Hope this may help.

zafarjcp · **Joined:** Sun May 18, 2008 1:21 pm **Posts:** 13

First give your mapping information.

2nd mean while try these 3 things

1. create a self join in database
2. in mapping file add a relation to itself.
3. while using ICriteria use Expression.Add(first condition Eq).Add(2nd Condition Eq)

Hope this may help.

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

This is a question about syntax and if this can be accomplished with NHibernate. You can dream up any example to illustrate if it is.

Here is a quote from SQL BooksOnline regarding what I'm trying to accomplish:

Quote:

ON <search_condition>
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are frequently used

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

It seems that you can only add conditions to the WHERE clause using ICriteria. I need to add it to the ON clause! (see example in original post)

Here is a post with the same issue asked a different way:
http://forum.hibernate.org/viewtopic.php?t=952606&highlight=

Thanks.

sivesind · **Posted:** Mon Sep 15, 2008 11:27 am

Hello gunslinger!

Did you ever get an answer on this? I'm trying to do a very similar thing with HQL, and it would be nice to know if I should just give up or if there is a possible solution out there. I'll switch to the criteria API if necessary.

Sincerely Yours.

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

Sorry, but I have not. From what I have read (and understood) is that this seems to be a limitation of the ORM. You just have to work around it. :(

Please post back here if you ever come across a solution and I will try to do the same.

Good Luck!

zafarjcp · **Joined:** Sun May 18, 2008 1:21 pm **Posts:** 13

any solution plz????

zafarjcp · **Joined:** Sun May 18, 2008 1:21 pm **Posts:** 13

any solution plz????

gunslinger · **Joined:** Mon Feb 04, 2008 1:48 pm **Posts:** 30

zafarjcp - no solution. :(