5

Is there a way to use an ICriteria result as a 'base' for a subsequent criteria query?

For example if I would like to create a query

SELECT department_id, sum(cost) AS total
FROM payment
GROUP BY payment.department_id

storing the result as query0, and then execute the query

SELECT department.name, total
FROM department, query0
JOIN LEFT ON department.id=query0.id
WHERE total > 3

I do not want to have one single huge query executed all at once (which would be the result of creating an ICriteria with subqueries). Note that I have a selection/ restriction on a result of the first query and at the same time including one of its columns in the second query's projection.

The criteria is generated dynamically using strings to identify the classes.

Dani
  • 2,602
  • 2
  • 23
  • 27
  • 1
    Im not familir with ICriteria, but couldnt the first query serve as the base or rootquery and then wrap a new select around it?? – Mad Dog Tannen Nov 27 '13 at 15:17
  • 1
    Maybe you should just filter department_ids of your query0 with total > 3 and then get the departements with a IN clause in your second query. Nice post from Ayende on this issue when the IN list is really big : http://ayende.com/blog/2583/nhibernates-xml-in as well as a non broken link to XmlIn.cs : http://code.google.com/p/hornget/source/browse/trunk/package_tree/frameworks/rhino.tools/rhino/patch/commons/Rhino.Commons.NHibernate/NHibernate/XmlIn.cs?spec=svn78&r=78 – jbl Nov 27 '13 at 16:24
  • 1
    @Kay Nelson This is exactly the approach of the detached criteria with a merged, but single query. @jbl I'd still need the total in the projection. And this solution would just work in this particular case (e.g. not if the join is over `department.id=query0.id AND department.XYZ==query0.XYZ`) – Dani Nov 27 '13 at 19:21
  • 1
    I guess creating a view for your query0 and mapping this view might be an option – jbl Nov 28 '13 at 10:42
  • 1
    Do you mean creating views in the database? This is too database-specific. Thanks to NHibernate, the queries and thus the application remains database-independent and does not require SQL strings that must adhere to the db-specific syntax – Dani Dec 10 '13 at 08:46
  • 1
    You cannot really do that, since it is not (as far as I know) supported by SQL databases. You cannot really "store" query results and use it in another query without using views or some kind of temp table (and as you pointed out, that might defeat point of using nHibernate). – Marian Polacek Dec 16 '13 at 15:06

1 Answers1

1

The closest thing I can think of to this is a Common Table Expression (the SQL WITH statement). Unfortunately NHibernate doesn't seem to have any good abstractions for dealing with CTEs, but here is how your query might look in SQL Server:

WITH query0 AS (
    SELECT department_id AS id, sum(cost) AS total
    FROM payment
    GROUP BY payment.department_id
)
SELECT department.name, total
FROM department, query0
WHERE department.id=query0.id
AND total > 3;

SQL Fiddle: http://sqlfiddle.com/#!3/8e6877/7

dwurf
  • 12,393
  • 6
  • 30
  • 42