5

I've an entity model like this:

public class Request
{
    public virtual IList<Response> Responses { get; set; }
}

public class Response
{
    public virtual DateTime Timestamp { get; set; }
    public virtual bool Success { get; set; }
}

I'm trying to create a Query that will give me all Request where its latest Response (regarding to its Timestamp) is sucess. How can this be done?

dhrm
  • 14,335
  • 34
  • 117
  • 183

2 Answers2

7

As almost always, NHibernate does have answer for this. What we are here trying to achieve would be a SQL Statement lookin like this:

// final Request selection
SELECT request.[RequestId] 
 FROM [Request] request 

   // Only requests, which are successful, and have Max(date)
   WHERE request.[RequestId] IN 
   (
     SELECT successResponse.RequestId as y0_ 
      FROM [Response] successResponse 

        // response which max date is equal to the upper response
        // and which RequestId corresponds with supper upper Request
        WHERE EXISTS
        (
          SELECT maxResponse.RequestId as y0_
           , max(maxResponse.[DateTime]) as y1_           
           FROM [Response] maxResponse 

           // do the MAX only for current Request
           WHERE maxResponse.RequestId = successResponse.RequestId 
           GROUP BY maxResponse.RequestId 

           // assure that the Response match is on the max DateTime
           HAVING max(maxResponse.[DateTime]) = successResponse.[DateTime]
        ) 
        AND successResponse.[Success] = 1
   )

Notes:

  1. Expecting the Response does have RequestId
  2. above was used C# // comment instead of SQL --

And now the magic of NHibernate and QueryOver:

// This declaration will allow us, to use a reference from middle SELECT
// in the most deeper SELECT
Response response = null;

// the most INNER SELECT
var maxSubquery = QueryOver.Of<Response>()
   .SelectList(l => l
    .SelectGroup(item => item.RequestId)
    .SelectMax(item => item.DateTime)
    )
    // WHERE Clause
   .Where(item => item.RequestId == response.RequestId)
   // HAVING Clause
   .Where(Restrictions.EqProperty(
      Projections.Max<Response>(item => item.DateTime),
      Projections.Property(() => response.DateTime)
    ));

// the middle SELECT
var successSubquery = QueryOver.Of<Response>(() => response)
    // to filter the Request
    .Select(res => res.RequestId)
    .WithSubquery
    .WhereExists(maxSubquery)
    // now only these wich are successful
    .Where(success => success.Success == true)
    ;

At this moment we have to inner SUB SELECTs, nested. let's use them:

// the most outer SELECT
var query = session.QueryOver<Request>();
query.WithSubquery
    // our Request ID is IN(...
    .WhereProperty(r => r.ID)
    .In(successSubquery);

var list = query
    .List<Request>();

Final notes, I am not discussing the concept. Not the performance. I would use rather a setting on response "IsActive" and make it easier ... this is just the answer how to do that...

Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • Thanks for your reply. What if I cannot expect that my *Responses* always have an *RequestId*? – dhrm Dec 17 '13 at 07:31
  • Honestly, I see it this way: you shold introduce some *Business logic*, which will be able to mark the latest record as `IsActive` (new column). If you won't go this way... then you won't be able to that SQL statement without the column `RequestId` - for sure. So if this column is there... it **should be mapped in your Reponse** entity... as a relation. Then you can use my way... no other option I would say ;) good luck with NHibernate – Radim Köhler Dec 17 '13 at 07:37
  • Radim, can you explain what the **SelectList** does here? Also, would it work just as well to order the Responses in **maxSubquery** by DateTime descending and then Take(1)? – Michael Dec 31 '13 at 07:25
  • @Michael The `SelectList` represents the projection, i.e. the set of columns/formulas we want to return. So the very deep SELECT must return 2 values which are be used in upper queries *(to find a matching records)*. It could be `.Select()` with *params* as well I would say... To use Take(1) in subquery in this case does not play any role. The DB engine will create the correct execution plan, and once it will find match.. it will stop. so on this level it is not needed. If I do understand your point well. Anyhow, any optimisation from our side, helping DB engine... could be welcome ;) – Radim Köhler Dec 31 '13 at 08:06
0

I'll take a stab at this, here's some linq (using Query instead).

session.Query<Request>()
    .Where(request => 
        request.Responses.Count() > 0 && 
        request.Responses.OrderByDescending(response => response.Timestamp)
                         .First()
                         .Success);

No idea whether this works.

Matthew
  • 24,703
  • 9
  • 76
  • 110