select pr.AbstractProduct from Price pr where pr.Quantity = 1 and
(pr.Expires > :expire or pr.Expires is null) order by pr.Amount
This HQL?
yes.
Gmail code
var criteria=session.CreateCriteria(typeof(Price),"pr")
.Add(Restrictions.Eq("pr.Quantity",1))
.Add(
Restrictions.Or(
Restrictions.Gt("pr.Expires",theDate),
Restrictions.IsNull("pr.Expires")))
.AddOrder(Order.Asc("pr.Amount"))
Tuna Toksöz
Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz
http://tunatoksoz.com
http://twitter.com/tehlike
On Tue, Jul 7, 2009 at 4:45 AM, zhibin <[email protected]> wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
