[odb-users] How to create an object view that has a self join

[Boris Kolpackov]

Hi Steve,

Hales, Steve <Steve.Hales at garmin.com> writes:

> #pragma db object
> class person
> {
>   #pragma db id
>   unsigned long id_;
>   std::string name_;
>   #pragma db value_not_null inverse(person_)
>   std::vector<odb::lazy_weak_ptr<family_member>> family_members_;
> };
> 
> #pragma db object
> class family
> {
>   #pragma db id
>   unsigned long id_;
>   std::string name_;
>   #pragma db value_not_null inverse(family_)
>   std::vector<odb::lazy_weak_ptr<family_member>> family_members_;
> };
> 
> #pragma db object
> class family_member
> {
>   #pragma db id
>   unsigned long id_;
>   #pragma db not_null
>   odb::lazy_shared_ptr<person> person_;
>   #pragma db not_null
>   odb::lazy_shared_ptr<family> family_;
> };
> 
> Now, I need to create an object view that has a self join on the
> family_members table so that, given a person ID, I can get all persons
> that are members of the families for that person. Here is my failed
> attempt:
> 
> #pragma db view \
>     object(person) \
>     object(family_member) \
>     object(family_member = fm2 inner : family_member::family_)
> struct person_view
> {
>   std::shared_ptr<person> person_;
> };

How does it fail? Don't just say "it doesn't work", it is not helpful.
Be specific (error messages, query you get, etc).


> Here is the working SQL query for this self join:
> SELECT
>   'persons'.*
>     FROM
>   'persons'
>     INNER JOIN
>   'family_members'
>   ON 'family_members'.'person' = 'persons'.'id'
> INNER JOIN
>   'family_members' as fm2
>   ON 'family_members'.'family' = fm2.'family'
>     WHERE
>   fm2.'person_id¹ = ?

The last JOIN is not based on the object relationship, so you will need
to spell the condition explicitly:

object(family_member = fm2 inner: fm2::family_ == family_member::family_)

Also you may want to add 'inner' to the first family_member object that
you have in the view.

Boris