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

[Aldo Laiseca]

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. 

Thanks



> El 1 abr 2024, a las 2:57, Boris Kolpackov <boris at codesynthesis.com> escribió:
> 
> Aldo Laiseca <alaiseca at gmail.com> writes:
> 
>> I need to find the most recent T1 (based on field timestamp_action) having a foreign key to T2 matching a particular value of T2.name. In SQL, something like this: 
>> 
>> select t1.* from t1
>>  where t1.timestamp_action = (select max(t1.timestamp_action) 
>>                               			 from t1 join t2 on t1.id_t2 = t2.id
>>                                   		   where t2.name = ?))
>> 
>> 
>> How could I write such a query using ODB?
> 
> By using a view:
> 
> https://www.codesynthesis.com/products/odb/doc/manual.xhtml#10
> 
> Generally, a lot of "how do I do X in ODB" can be answered by at least
> skimming through the documentation once so that you have an idea of the
> functionality available.