SQLAlchemy - relationship limited on more than just the foreign key

Question

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?

Answer 1

Try installing relationship after both classes are created:

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 not correct: revisions property loads data from database while you haven't added them to session.

Update: The problem in your code is that primaryjoin parameter is not string, so it's not evaluated. Using 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,
    )