[odb-users] Re: How to perform an aggregate subquery

[Boris Kolpackov]

Aldo Laiseca <alaiseca at gmail.com> writes:

> Yes, actually I had figured out that a view was the approach to
> follow. I declared this: 
> 
> #pragma db view object(T1) object(T2)
> struct ViewT1T2 {
> 
>     #pragma db column("max(" + T1::timestamp_action + ")")
>     #pragma db type("TIMESTAMP")
>     boost::posix_time::ptime maxTimestamp;
> };
> 
> However, after that, I don’t know how to recover the entire T1
> object having the max value. Right now I solved my requirement
> in two steps:
> 
> 1. Execute a query_value against the view to grab the maximum
>    timestamp value.
> 2. Execute a query_one against the object T1 to get the row having
>    the row matching the value obtained in step 1. 
> 
> Is there any way to do the above in one step? I read about object loading
> views; however, I don’t see how to include a max condition in a query in
> an object loading view.

The first question that you need to answer is how to do this in SQL.
Then try to map this answer to an appropriate ODB view. In SQL you
cannot just say `SELECT ... WHERE max(timestamp_action)` (well, maybe
there is support for something like this in the specific database that
you are using).