[odb-users] Many to many relationships - Insertion and deletion

[Boris Kolpackov]

Hi Nicolas,

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.


> project->members.push_back(account);
> db->update(project);
> 
> 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.

Boris