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

[Hales, Steve]

I have three tables: persons, families and family_members. The
family_members table provides a many-to-many relationship between persons
and families. (In my system, a person can be a member of many families.) I
have successfully created ODB persistent objects for all three tables:

#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_;
};

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¹ = ?

Thanks in advance for your help.
- Steve


________________________________

CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient(s) and contain information that may be confidential and/or legally privileged. If you have received this email in error, please notify the sender by reply email and delete the message. Any disclosure, copying, distribution or use of this communication (including attachments) by someone other than the intended recipient is prohibited. Thank you.