[odb-users] Loading persistent object using custom joins

[Boris Kolpackov]

Hi Patrick,

Patrick Rotsaert <Patrick.Rotsaert at intoit.be> writes:

> I know joins can be done with views, but if I understand correctly,
> views contain only a subset of the objects involved.

More precisely, views can contain a subset or a full set of data
members from one or more objects.


> #pragma db object
> class employer
> {
>   ...
> 
>   #pragma db id
>   unsigned long id_;
> 
>   std::string name_;
> };
> 
> #pragma db object
> class employee
> {
>   ...
> 
>   #pragma db id
>   unsigned long id_;
> 
>   std::string first_;
>   std::string last_;
> 
>   unsigned short age_;
>   
>   shared_ptr<employer> employer_;
> };
> 
> Suppose I'd like to query all employers that have employees under 30 
> years of age.

In ODB you can reference data members from the pointed-to objects (one
level deep) in queries. So, if, for example, you wanted to find all the
employees that have "Example, Inc" as an employer, then you could do:

typedef odb::query<employee> query;
typedef odb::result<employer> result;

result r (db.query<employee> (query::empployer->name == "Example, Inc"));

Your query is a bit different, however. First of all, there is no "back"
pointer from employer to employees, but even if we added one, it would
have been a container and there is no support for using containers in
queries, yet.


> In SQL speak, that would be something like:
> 	SELECT DISTINCT employer.*
> 	FROM employee
> 	NATURAL JOIN employer
> 	WHERE employee.age < 30
>
> How can this be done, so I have an odb::result<employer> to work with?

The only way to achieve this that I can think of is to use a view to get
the list of ids of all the employers that have employees under 30. Then,
you can use these ids to load each employer:

#pragma db view object(employer) \
                object(employee) \
                query((?) + "GROUP BY" + employer::id_)
struct employers
{
  #pragma db column(employer::id_)
  unsigned long id;
};

typedef odb::query<employers> query;
typedef odb::result<employers> result;

result r (db.query<employers> (query::employee::age < 30));

for (const employers& es: r)
{
  shared_ptr<employer> e (db.load<employers> (es.id));
  ...
}

Boris