Menu
▾
▴
[Hibernate-commits] Hibernate2/doc/reference/src query_language.xml,1.10,1.11
|
From: <one...@us...> - 2003-04-21 06:23:46
|
Update of /cvsroot/hibernate/Hibernate2/doc/reference/src
In directory sc8-pr-cvs1:/tmp/cvs-serv4135/reference/src
Modified Files:
query_language.xml
Log Message:
more query examples
Index: query_language.xml
===================================================================
RCS file: /cvsroot/hibernate/Hibernate2/doc/reference/src/query_language.xml,v
retrieving revision 1.10
retrieving revision 1.11
diff -C2 -d -r1.10 -r1.11
*** query_language.xml 11 Apr 2003 07:12:21 -0000 1.10
--- query_language.xml 21 Apr 2003 06:23:42 -0000 1.11
***************
*** 613,620 ****
group by foo.id]]></programlisting>
! <para>Note: You may use the <literal>elements</literal> and <literal>indices</literal> constructs
! inside a select clause, even on databases with no subselects.</para>
! <para>A <literal>having</literal> clause is also allowed.</para>
<programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
--- 613,624 ----
group by foo.id]]></programlisting>
! <para>
! Note: You may use the <literal>elements</literal> and <literal>indices</literal> constructs
! inside a select clause, even on databases with no subselects.
! </para>
! <para>
! A <literal>having</literal> clause is also allowed.
! </para>
<programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
***************
*** 622,626 ****
group by cat.color
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)]]></programlisting>
!
</sect1>
--- 626,648 ----
group by cat.color
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)]]></programlisting>
!
! <para>
! SQL functions and aggregate functions are allowed in the <literal>having</literal>
! and <literal>order by</literal> clauses, if supported by the underlying database (ie.
! not in MySQL).
! </para>
!
! <programlisting><![CDATA[select cat
! from eg.Cat cat
! join cat.kittens kitten
! group by cat
! having avg(kitten.weight) > 100
! order by count(kitten) asc, sum(kitten.weight) desc]]></programlisting>
!
! <para>
! Note that neither the <literal>group by</literal> clause nor the
! <literal>order by</literal> clause may contain arithmetic expressions.
! </para>
!
</sect1>
***************
*** 655,666 ****
Hibernate queries can be quite powerful and complex. In fact, the power of the query language
is one of Hibernate's main selling points. Here are some example queries very similar to queries
! that I used on a recent project.
</para>
!
<para>
! This query counts the number of payments in each status, excluding all payments in the
<literal>AWAITING_APPROVAL</literal> status where the most recent status change was made by the
! current user. It translates to an SQL query with two inner joins and a subselect against the
! <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal> and
<literal>PAYMENT_STATUS_CHANGE</literal> tables.
</para>
--- 677,735 ----
Hibernate queries can be quite powerful and complex. In fact, the power of the query language
is one of Hibernate's main selling points. Here are some example queries very similar to queries
! that I used on a recent project. Note that most queries you will write are much simpler than these!
</para>
!
<para>
! The following query returns the order id, number of items and total value of the order for all
! unpaid orders for a particular customer and given minimum total value, ordering the results by
! total value. In determining the prices, it uses the current catalog. The resulting SQL query,
! against the <literal>ORDER</literal>, <literal>ORDER_LINE</literal>, <literal>PRODUCT</literal>,
! <literal>CATALOG</literal> and <literal>PRICE</literal> tables has four inner joins and an
! (uncorrelated) subselect.
! </para>
!
! <programlisting><![CDATA[select order.id, sum(price.amount), count(item)
! from Order as order
! join order.lineItems as item
! join item.product as product,
! Catalog as catalog
! join catalog.prices as price
! where order.paid = false
! and order.customer = :customer
! and price.product = product
! and catalog.effectiveDate < sysdate
! and catalog.effectiveDate >= all (
! select cat.effectiveDate
! from Catalog as cat
! where cat.effectiveDate < sysdate
! )
! group by order
! having sum(price.amount) > :minAmount
! order by sum(price.amount) desc]]></programlisting>
!
! <para>
! What a monster! Actually, in real life, I'm not very keen on subqueries, so my query was
! really more like this:
! </para>
!
! <programlisting><![CDATA[select order.id, sum(price.amount), count(item)
! from Order as order
! join order.lineItems as item
! join item.product as product,
! Catalog as catalog
! join catalog.prices as price
! where order.paid = false
! and order.customer = :customer
! and price.product = product
! and catalog = :currentCatalog
! group by order
! having sum(price.amount) > :minAmount
! order by sum(price.amount) desc]]></programlisting>
!
! <para>
! The next query counts the number of payments in each status, excluding all payments in the
<literal>AWAITING_APPROVAL</literal> status where the most recent status change was made by the
! current user. It translates to an SQL query with two inner joins and a correlated subselect
! against the <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal> and
<literal>PAYMENT_STATUS_CHANGE</literal> tables.
</para>
***************
*** 712,716 ****
<para>
! For some databases, we would need to do away with the subselect.
</para>
--- 781,785 ----
<para>
! For some databases, we would need to do away with the (correlated) subselect.
</para>
|
