I used "explain" in postgresql to see the query plans. It's done on a
very small data set.
The 2 SQL statement I mentioned above generate same query plan.
Sort (cost=8494.29..8495.19 rows=360 width=40)
Sort Key: ((subplan))
-> Seq Scan on "AbstractProduct" this_ (cost=0.00..8479.00
rows=360 width=40)
SubPlan
-> Aggregate (cost=23.50..23.52 rows=1 width=18)
-> Seq Scan on "Price" this_0_ (cost=0.00..23.50
rows=1 width=18)
Filter: ((abstractproduct_id = $0) AND (quantity
= 1::numeric))
the query plan of Fabio's approach:
Sort (cost=36.33..36.34 rows=4 width=209)
Sort Key: price0_.amount
-> Hash Join (cost=21.30..36.29 rows=4 width=209)
Hash Cond: (abstractpr1_.id = price0_.abstractproduct_id)
-> Seq Scan on "AbstractProduct" abstractpr1_
(cost=0.00..13.60 rows=360 width=191)
-> Hash (cost=21.25..21.25 rows=4 width=26)
-> Seq Scan on "Price" price0_ (cost=0.00..21.25
rows=4 width=26)
Filter: (quantity = 1::numeric)
look like much better as of performance.
The only drawback is: each product possibly have multiple unit prices
(with different Expires). But this can be handled in the C# side.
Thanks very much!
and is it possible to write the HQL with Criteria API?
On Jul 6, 9:20 pm, Fabio Maulo <[email protected]> wrote:
> select pr.AbstractProduct from Price pr where pr.Quantity = 1 and
> (pr.Expires > :expire or pr.Expires is null) order by pr.Amount
>
> 2009/7/6 zhibin <[email protected]>
>
>
>
>
>
> > I have the following 2 classes:
>
> > public abstract class AbstractProduct : Entity
> > {
> > public virtual ISet<Price> Prices { get; set; }
> > }
>
> > public class Price : Entity
> > {
> > public virtual AbstractProduct AbstractProduct { get; set; }
> > public virtual decimal Amount { get; set; }
> > public virtual decimal Quantity { get; set; }
> > public virtual DateTime? Expires {get; set;}
> > }
>
> > I want to get products ordered by unit price. The SQL I supposed is:
> > select p0.*, (select min(pr.amount) from "Price" pr
> > where pr.abstractproduct_id=p0.id
> > and pr.Quantity = 1
> > and (pr.expires>'2009-7-1' or
> > pr.expires is null)
> > )as amount
> > from "AbstractProduct" p0;
> > order by amount;
>
> > I tried it in Postgresql, it returns correct result. but I don't know
> > how to code it with the Criteria API.
>
> > Can anybody help me?
> > Thanks!
>
> --
> Fabio Maulo
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
