@before_insert and ManyToMany relationships

[lea]

Hi all,
i have an entity Question with a ManyToMany relationship answer_codes.
In an @before_insert or @after_insert, I'd like to add answer codes to
the answer_codes relationship. When I then flush or commit the
question is inserted into the database, the answer code relationship
entities however are not (the answer code entities already exist in
the database). Did I do something wrong or is this supposed to be like
that?

class Question(Entity):
...
answer_codes = ManyToMany('AnswerCode',
tablename='rel_question_to_answer_code',
onupdate='CASCADE',
ondelete='CASCADE')

@before_insert
def add_answer_codes(self):
for answer_code in self.valid_answer_codes:
self.answer_codes.append(AnswerCode.get_by
(code=answer_code))

Cheers,
Lea

[Gaetan de Menten]

Two problems here:
1) if you add objects in a relation in an "event listener", it won't
have any effect directly. The list of objects to flush to the database
has already been computed and it's "too late" to do so at this point.
Your changes will only be computed on the next flush.
2) you query using the ORM within the even listener, which will (in
most cases) cause a flush to happen, which is a bad thing since you
are already in the process of a flush (before_insert is a hook which
is called by a flush).

I suggest you use "after_insert" instead, and use the SQL layer to
insert those records. It would produce a second query to the database,
so that might not be optimal.

Another option would be to use a session extension instead:
before_flush (or any other). But then you'd have to use an isinstance
call to check that it is a Question instance, and also check that it's
actually a new instance, not an old one, that you are updating, kinda
tedious...

Another option would be to bring this to the SQLAlchemy list where you
might receive other suggestions and you could potentially ask for a
new feature to ease that use case.

For a bit more details, see:
http://www.sqlalchemy.org/docs/05/reference/orm/interfaces.html?highlight=mapperextension#sqlalchemy.orm.interfaces.MapperExtension.before_insert

--
Gaëtan de Menten
http://openhex.org