[odb-users] Many to many relationships - Insertion and deletion
boris at codesynthesis.com
Mon Aug 22 10:43:45 EDT 2011
Nicolas ALBEZA <n.albeza at gmail.com> writes:
> I'm currently using ODB's many-to-many relationships in my projects, and i
> was wondering how to insert and delete entires efficiently.
> My classes look like (most of it omitted for clarity):
> class Project
> #pragma db value_not_null unordered
> list<lazy_shared_ptr<Account> > members;
> class Account
> #pragma db value_not_null inverse(members)
> list<lazy_weak_ptr<Project> > projects;
> Currently, to add an Account to a Project, i'm doing something along the
> lines of :
> shared_ptr<Account> account = db->find(...);
> shared_ptr<Project> project = db->find(...);
You don't really need to load the Account object (unless you need it
later). Instead, you can create an "unloaded" lazy pointer:
lazy_shared_ptr<Account> account (*db, account_id);
The 'inverse' example uses this technique.
> Unfortunately, when looking at MySQL's query log, i can see the following
> queries being executed :
> - A full update of the project entry
> - Deletion of entries in the junction table
> - And finally, insertion of an entry in the junction table
> The two first steps seem unnecessary, so i was wondering if there was a
> better approach to do this.
Unfortunately, I am afraid this is as efficient as it can get right
now. We still need to work on the state change detection and more
optimal container handling.
If you had a one-to-many relationship, then making the 'many' side
inverse (and thus getting rid of the container table) would have
been a viable optimization.
More information about the odb-users