Inner Join with a subquery (QueryOver)

[Felipe Oriani]

Hello guys, We are migrating a legancy system to .Net and we use NHibernate on the Data Access Layer. 

I would like to know, how can we do a inner join with a subquery with QueryOver? For sample:

select wo.id, 

       wo.description,

       op.total

from work_order wo

     inner join (select o1.operation_id, sum(o2.amount * o2.unit_price) total

                 from operation o1

                      inner join operation_item o2 on o2.operation_id = o1.id

                 group by oo.work_order_id) op

                on op.work_order_id = wo.id     

We have been using QueryOver and it works great for our scenarios, but we have one that is a little more complicated like this (and we will have more like this). 

We know we can built a subQuery on the from clausule, but, we want a better query.

Thank you.

--

______________________________________
Felipe B Oriani

felipe...@gmail.com

[Jeff Buda]

"There are two places you can use subqueries in a QueryOver query: the SELECT clause and the WHERE clause. Unfortunately you cannot use a subquery in the FROM clause of a QueryOver query." - post

If my team has to make nested subqueries we define a database view and then create a model that maps to the view.

[Felipe Oriani]

I think I've read it somewhere. We cannot make a View because it is a dynamic scenario. It will be used over some parts of the system. We solve it on the SELECT with a subquery, but we knot the inner join subquery has a better performance.

Thank you Jeff.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscribe@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.