SQLAlchemy - Relationships Are Limited Not Only by a Foreign Key

Question

I have a wiki db layout with Page and Revisions . Each editor has a page_id link that links to a Page link, a Page to a link page; each page has an all_revisions relationship to all of its changes. It is still so common.

But I want to implement different eras for pages: if the page has been deleted and recreated, new versions have a new epoch . To find the correct changes, each page has a current_epoch field. Now I want to provide a Revisions relation on a page that contains only its revisions, but only those where the epochs coincide.

Here is what I tried:

 revisions = relationship('Revision', primaryjoin = and_( 'Page.id == Revision.page_id', 'Page.current_epoch == Revision.epoch', ), foreign_keys=['Page.id', 'Page.current_epoch'] ) 

Full code (you can run it as it is)

However, this always raises an ArgumentError: it was not possible to determine the direction of the relation for the primaryjoin ... `condition . I tried everything that occurred to me, it did not work.

What am I doing wrong? This is a bad approach for this, how can this be done differently than with relationships?

Answer 1

Try linking after creating both classes:

 Page.revisions = relationship( 'Revision', primaryjoin = (Page.id==Revision.page_id) & \ (Page.current_epoch==Revision.epoch), foreign_keys=[Page.id, Page.current_epoch], uselist=True, ) 

BTW, your test is wrong: revisions feature loads the data from the database until you have added them to the session.

Update . The problem in your code is that the primaryjoin parameter primaryjoin not a string, so it is not evaluated . Using a string in primaryjoin works fine:

 class Page(Base): # [skipped] revisions = relationship( 'Revision', primaryjoin = '(Page.id==Revision.page_id) & '\ '(Page.current_epoch==Revision.epoch)', foreign_keys=[id, current_epoch], uselist=True, )