[odb-users] deleting entries on relation table

[Boris Kolpackov]

Hi Simón Emmanuel,

Simón Emmanuel Gutiérrez Brida <simon.gutierrez.brida at gmail.com> writes:

> User with these members
> 
> #pragma db id
> std::string id;
> 
> #pragma db value_not_null
> std::vector<std::tr1::shared_ptr<Pattern> > patterns;
> 
> Pattern with these members
> 
> #pragma db id
> std::string id;
> 
> #pragma db map type("XML")       \
>               as("TEXT")        \
>               to("XMLPARSE(DOCUMENT (?))") \
>               from("XMLSERIALIZE(DOCUMENT (?) AS TEXT)")
> #pragma db type("XML")
> std::string data;
> 
> And I get a table for each class plus a table that relates a User with a
> Pattern.

Generally, you can refer to related objects in queries one level deep
(see the end of the introduction to Chapter 6, "Relationships"). Though
note that there is no support for using containers in queries yet.

In this light, it actually makes sense to change your relationship
a bit. Firstly, we make it bi-directional so that we can refer to
both objects whether you are querying for User or Pattern. Secondly,
it makes sense to move the direct (non-inverse) pointer to Pattern
since then you get rid of the linking table (note that here I assume
you have the one(User)-to-many(Pattern) relationship, and not many-to-
many). 
 
So now we have:

User:

#pragma db value_not_null inverse(user)
std::vector<std::tr1::shared_ptr<Pattern> > patterns;

Pattern:

#pragma db not_null
std::tr1::weak_ptr<User> user;

You can change the shared/weak arrangement around. And also don't
forget to use the session since it is a bi-directional (recursive)
relationship.

Once this is done, we can clean up the User table like this:

db.erase_query<Pattern> (odb::query<Pattern>::user->id == 123);

Also let me mention an alternative approach that will be available
in the next release of ODB. There will be support for the ON DELETE
clause which can be used to automatically clean up referencing
objects. This feature is already implemented and you can test it
(or start using it) in a pre-release, if you would like. 

Boris