an ugly stuff would be to use "from AbstractProduct a where a.Id in (subquery that returns the ids of AbstractProduct)"
as criteria. 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 1:53 PM, zhibin <[email protected]> wrote: > > I just tried, the projection merely return the id of AbstractProduct, > no other properties. > > I also tried this way: > var criteria = Session.CreateCriteria(typeof(Price), "pr") > .Add(Restrictions.Eq("pr.Quantity", 1m)) > .Add(Restrictions.Or(Restrictions.Gt("pr.Expires", > DateTime.Now), > Restrictions.IsNull("pr.Expires"))) > .AddOrder(global::NHibernate.Criterion.Order.Asc("pr.Amount")) > .CreateCriteria("AbstractProduct") > .List<Price>(); > > it returns a List of Prices which AbstractProduct has been fetched > eagerly, > then use C# to do the remaining convertion. > > Thanks! > > On Jul 7, 6:23 pm, Tuna Toksoz <[email protected]> wrote: > > .SetProjection(Projections.Property("pr.AbstractProduct")) > > > > hmm this may not work... Can you try? As far as I remember, there is no > way > > to project on entity. > > > > Tuna Toksöz > > Eternal sunshine of the open source mind. > > > > > http://devlicio.us/blogs/tuna_toksozhttp://tunatoksoz.comhttp://twitter.com/tehlike > > > > On Tue, Jul 7, 2009 at 11:46 AM, Tuna Toksoz <[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 > > > > > 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 -~----------~----~----~----~------~----~------~--~---
