When using m2m association collections, strange behavior occurs, if the session is not committed.

[Boris Kozinsky]

If the session is not committed: When appending to collection the association object is not created. When the association object is created, the collection is not updated.

If the session is committed: Both association objects and collections are updated, but using difference routes it seems, which is reflected in the way object initialization is happening.

Is this normal? I would think that the results should not depend on whether the session is committed to the database. This would be much easier for testing, if everything was updated and available for querying even without a commit.

Note: in the attached file the association table UserKeywords has an extra field (special_key), but that is not relevant for this report.

[Mike Bayer]

the mistake you're making here is mixing the use of a "Secondary" relationship with association proxy. These are entirely unrelated relationships, and you should be doing mutations on only one or the other, and not both; furthermore, there is no automatic synchronization between these two unrelated relationships. The session's commit() expires all collections by default, which is why you see the correct data loaded after a commit.

see: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#association-object, especially the green note at the end, as well as http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#committing.

[Mike Bayer]

behavior here is normal.

[Boris Kozinsky]

Hi Mike,

Thanks for the speedy reply.

Can you please clarify - what is the correct/preferred way to setup collections in this case? The goal is to be able to query while avoiding many joins to the association table.

I want to be able to do something like this, to find users that share a keyword:

session.query(User).join(User.keywords).\ join(Keyword.users, aliased=True).filter(User.name=='log')

This actually works... Is this expected and is this a legitimate mechanism to construct queries spanning several m2m relations?

[Mike Bayer]

if you have User->UserKeyword->Keyword, the rationale for the association proxy is so that you can say User.keywords or Keyword.users while still including the UserKeyword association class in the middle of the system.

The association proxy currently supports an accessor attr (http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.attr) to make the join() system less verbose. Assuming keywords = association_proxy('user_keywords', 'keyword'):

print session.query(User).join(*User.keywords.attr)

note that at the SQL level, there's no way around having two JOIN keywords here as long as you have three tables in play.

SQLAlchemy doesn't call this an "m2m" relationship, as we've mapped the center table; we call it an "association relationship". Same thing at the DB level but from a mapping perspective it's different than a relationship() with secondary.