CODEWITHSUNDEEP
nhibernateprojectionqueryover
Zen-C
Zen-C

Reputation: 71

nHibernate aggregate query

I'm attempting to put the following SQL query into an nHiberate QueryOver statement and I'm failing miserably.

Select top 15 percent
    People.personId
,People.Name
    ,SUM(Marks.Score)
from 
    People
    inner join [sessions] on [sessions].PersonId = People.PersonId
    inner join Marks on Marks.SessionId = [Sessions].SessionId
group by
    People.Name
    ,People.PersonId
order by
    SUM(Marks.Score) asc

My objects are:

With the obvious FK constraints (people --> Session --> Marks).

Might someone give me some advice?

Many thanks

Upvotes: 0

Views: 1381

Answers (2)

J. Ed
J. Ed

Reputation: 6742

joins are easy enough with .Join(). If you've defined those relations in your mappings, then you don't even have to specify how the join is made.
I usually find it more convenient to use JoinAlias. Aggregations are done using Projections (see aliases and projections here).

You'll need a simple sum projection, and a grouping projection, of course.

For the result, you can either use an object [] and cast it (as in the link above), or you can define a custom DTO, and use AliasToBeanResultTransformer. (here is a pretty old blog post; the syntax has now changed to allow for lambda expressions).

Concerning top 15 percent- I'm not sure.
QueryOver().Take() allows for a constant; not sure there's native support for percentages.
You could implement your own projection, though. see here.

Edit: a simple projection example
How lazy can I be? I just copied this from github.
It has joins, aliases, projections.. everything you need. I added a SUM projection just for fun.
Notice how all the other properties have to be grouped-by, of course: 

Customer customer = null;
    Employee employee = null;
    OrderHeader orderHeader = null;

    var orderHeaders = Session.QueryOver<Order>()
        .JoinAlias(o => o.Customer, () => customer)
        .JoinAlias(o => o.Employee, () => employee)
        .SelectList(list => list
            .SelectGroup(o => o.OrderedOn).WithAlias(() => orderHeader.OrderedOn)
            .SelectGroup(o => customer.Name).WithAlias(() => orderHeader.Name)
            .SelectGroup(o => employee.FirstName).WithAlias(() => orderHeader.FirstName)
            .SelectGroup(o => employee.LastName).WithAlias(() => orderHeader.LastName)
            .SelectSum(o => employee.Salary).WithAlias(() => orderHeader.JhonnysProjectionExample))
        .TransformUsing(Transformers.AliasToBean<OrderHeader>())
        .List<OrderHeader>();

Upvotes: 4

Diego Mijelshon
Diego Mijelshon

Reputation: 52735

TOP n PERCENT is a nonstandard SQL construct (a very useful one, I might add)

There's no support for that in any NH query method. Just keep it as SQL.

Upvotes: 0

Related Questions