On Apr 5, 2014, at 8:14 AM, Christian Kastner <debian.kvr...@gmail.com> wrote:
> Hi, > > I have a many-many relationship implemented as an Association Object. > When I add an association between a parent and a child, the list of > associations within the parent contains the child element twice. A > flush() does not help, but commit() resolves the issue. The > generated SQL only creates one INSERT, though (as expected). # SIDE QUESTION: Why is the following line required for association objects? # With an association TABLE (following the doc's many-many example), omitting # this works; with an assoc OBJECT the next line (append) raises IntegrityError a.parent = par par.children.append(a) because when you emit the phrase "par.children", Parent is a persistent object which potentially has a children collection already, however because this collection is expired (due to the Session.commit), the Session has no choice but to emit a SELECT (e.g. lazy-load) to retrieve the current par.children collection (which happens to be empty). The session autoflushes on every query by default, and an attempt is made to INSERT your Association object prematurely, as it is not yet associated with that parent; the append hasn't happened yet. The Association object is in the Session because you said "a.child = chi"; the backref "Child.parent_assocs" received an APPEND and that resulted in Association being cascaded into the session (read http://docs.sqlalchemy.org/en/latest/orm/session.html#save-update for a description of these mechanics, as well as http://docs.sqlalchemy.org/en/latest/orm/session.html#controlling-cascade-on-backrefs for background on how to change the backref behavior). By first assigning "a.parent = par", that does not trigger any lazy load and Association is now fully associated with both Parent and Child. When this occurs, it is *already present* in "par.children", again due to backrefs (an in-depth discussion of what "backref" means is at http://docs.sqlalchemy.org/en/latest/orm/relationships.html#linking-relationships-with-backref). So because you are appending to the collection twice, the object is present in the collection twice. > > Side question: what's the "proper" way to remove a many-many association > between two entities? As in: both entities continue to exist, it's just > the association that is removed (eg: entity "software", entity "os", > assoc "runs_on"). What I currently do is iterate over the associations > of the parent and remove the matched child, but that seems inefficient. If you're using an explicit association entity, you can remove it from either list, or detach either of parent or child which has the same effect and use "cacade='all, delete-orphan'" so that it is automatically deleted, see http://docs.sqlalchemy.org/en/latest/orm/session.html#delete for the beginning on how deletes are handled. to automate the bridge between parent/child and an association object, such that you could say parent.children.remove(child), use the association proxy: http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.