[odb-users] Object Loading Views with Containers / Alternatives?

[Raphael Palefsky-Smith]

Hello! When it first starts up, my application loads a top-level object
with a deep hierarchy of related objects stored in odb::vectors.

For example, say I have an Employer that has many Employees, who each have
many Pets. These are all stored in odb::vectors, though I'm open to using
other schemes to make this work. Some pseudocode:

#pragma db object
class employer {
    unsigned long id;
    string address;
    odb::vector<shared_ptr<employee>> employees; // non-lazy, need at
initial load
};

#pragma db object
class employee {
    unsigned long id;
    string name;
    odb::vector<shared_ptr<pet>> pets; // non-lazy, need at initial load
};

#pragma db object
class pet {
    unsigned long id;
    string nickname;
};

For a given Employer ID, I'd like to load all the associated Employees and
all their Pets (along with the non-relational data members on those
Employer/Employees/Pets). It would be great to perform this load all in one
shot with a single JOIN'd SELECT; the naive db.load() executes a ton of
individual statements and is far too slow. It seems like Object Loading
Views are designed exactly for this purpose, but I can't seem to get them
working with containers.

My object loading view looks like this:

#pragma db view object(employer) object(employee) object(pet)
struct employer_with_employees_and_pets {
    shared_ptr<employer> e;
};

When I query using this view, the stderr_tracer reports that individual
SELECTs are still being executed for each employee and pet instance. Is
there a way to modify the view so that it runs with a single SELECT?

If Object Loading Views are incompatible here, is there a workaround, even
if it involves hand-writing a bunch of SQL? Performance is more important
than source-code elegance in this specific case.

Thank you!