[odb-users] How to perform an aggregate subquery

[Aldo Laiseca]

Hi, I have a table, say T1, containing a field which is a foreign key to another table, say T2, and a timestamp field. As C++ classes, I have something like the following:

#pragma db object session pointer(std::shared_ptr< ClassForTableT1 >) table(“T1")
class ClassForTableT1 {
private:
   #pragma db id auto column("id") not_null
    unsigned long id;
   ….
   #pragma db column("id_t2") not_null
   std::shared_ptr<ClassForTableT2> objT2;

  #pragma db column(“timestamp_action") not_null
  #pragma db type("TIMESTAMP")
  boost::posix_time::ptime timestampAction;

   ...
} 

#pragma db object session pointer(std::shared_ptr< ClassForTableT2 >) table(“T2")
class ClassForTableT2 {
private:
   #pragma db id auto column("id") not_null
    unsigned long id;
   ….
   #pragma db column(“name") not_null
    std::string name;
   ...
} 


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?

As usual, any help will be greatly appreciated.