How to declared one-to-many if there are 2 fields for a same foreign key

Question

I'm new to python(sqlalchemy), and I'm learning to build web site with pylons and sqlalchemy.

I have a problem when I declare the relationship between models. I've tried it several hours, but failed. But I think it should be a basic question.

I have two classes: User and Article, user can create articles, and modified the other people's article(like wiki). So a user has created-articles and edited-articles.

class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True)
    title = ...

    user_id = Column(Integer, ForeignKey('users.id'))
    editor_id = Column(Integer, ForeignKey('users.id'))

    # relations
    user = relationship('User', backref='articles') # -> has error


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String(20))

    def __init__(self):
        pass

But there is an error displayed:

InvalidRequestError: One or more mappers failed to compile. Exception was probably suppressed within a hasattr() call. Message was: Could not determine join condition between parent/child tables on relationship Article.user. Specify a 'primaryjoin' expression. If this is a many-to-many relationship, 'secondaryjoin' is needed as well.

I tried to add primaryjoin to the line('has error'), but don't know what it should be. I tried some codes, but none works.

Thank you in advance!

Answer 1

Ah, thats obvious one.

Article class has two references to User, user_id and editor_id, so SQLA does not know which one of them to use for your relation. Just use explicit primaryjoin:

user = relation('User', backref='articles', primaryjoin="Article.user_id==User.id")