CODEWITHSUNDEEP
nhibernatesubqueryaggregationqueryover
Sascha
Sascha

Reputation: 31

NHibernate: Join a subquery (uses aggregation) with a table

I have a table containing records of machine movements between multiple locations.

As I only show the latest of them in a list, i have read only the data rows with the latest record date per machine until now in sql.

Select * 
From records
INNER JOIN
    (Select max(processdate) as maxDate, machinetag, machineno 
    from records
    group by machinetag, machineno ) as lrcd
on records.machineTag = lrcd.machineTag and records.machineno = lrcd.machineno 
and records.processDate = lrcd.maxDate

I have read many posts but I did not find any solution.

How can I implement this scenario with queryover?

Thany you very much in advance. Sascha

Upvotes: 2

Views: 216

Answers (3)

Sascha
Sascha

Reputation: 31

Now I can offer a (probably) working solution:

Dim r As Record = Nothing

Dim subquery = QueryOver.Of(Of Record)() _
                                         .Where(Function(rec) rec.R_MachineTag = r.R_MachineTag) _
                                          .And(Function(rec) rec.R_MachineNo = r.R_MachineNo) _
                                          .Select(Projections.Max(Of Record)(Function(rec) rec.R_ProcessDate)).Take(1)

Dim query = session.QueryOver(Function() r) _
                        .WithSubquery.WhereProperty(Function(rec) rec.R_ProcessDate).Eq(subquery) _
                        .List()

Thanks to cbp for giving me the hint ;)

Upvotes: 1

Sascha
Sascha

Reputation: 31

This statement represents my statement without inner joins:

    Select r.*
from records as r
where r.processDate =
   (Select max(processdate) as maxDate
    from records as lr
    where lr.machinetag = r.machinetag and
          lr.machineno  = r.machineno)

Upvotes: 0

cbp
cbp

Reputation: 25628

Inner joins can almost always be rewritten as a combination of a where clauses and projections.

Something like this (I'm writing without a compiler, sorry if there are any mistakes):

Select 
    records.*,
    (select max(processdate) as maxdate
     from records
     where lrcd.machineno = records.machineno)
     -- add more projections if you need them
From records
where records.processDate =
    (select max(processdate) as maxDate 
    from records
    where lrcd.machineno = records.machineno) as lrcd

This should be easy to implement with QueryOver now (subqueries can be implemented in projections or restrictions).

Don't worry about the duplicated code - the query plan will optimise it for you.

Upvotes: 1

Related Questions