I have a wiki db layout with Page and Revisions. Each Revision has a page_id referencing the Page, a page relationship to the referenced page; each Page has a all_revisions relationship to all its revisions. So far so common.
But I want to implement different epochs for the pages: If a page was deleted and is recreated, the new revisions have a new epoch. To help find the correct revisions, each page has a current_epoch field. Now I want to provide a revisions relation on the page that only contains its revisions, but only those where the epochs match.
This is what I've 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 may run that as it is)
However this always raises ArgumentError: Could not determine relationship direction for primaryjoin condition ...`, I've tried all I had come to mind, it didn't work.
What am I doing wrong? Is this a bad approach for doing this, how could it be done other than with a relationship?