.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_toksoz
http://tunatoksoz.com
http://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
-~----------~----~----~----~------~----~------~--~---
