How can I make property comparison able to be compiled to SQL expression in SQLAlchemy?

Question

There are two tables that one column of table A is pointing another table B's primary key.

But they are placed in different database, so I cannot configure them with foreign key.

Configuring via relationship() is unavailable, so I implemented property attribute manually.

class User(Base):
    __tablename__ = 'users'
    id = Column(BigInteger, id_seq, primary=True)
    name = Column(Unicode(256))


class Article(Base):
    __tablename__ = 'articles'
    __bind_key__ = 'another_engine'
    # I am using custom session configures bind
    # each mappers to multiple database engines via this attribute.

    id = Column(BigInteger, id_seq, primary=True)
    author_id = Column(BigInteger, nullable=False, index=True)
    body = Column(UnicodeText, nullable=False)

    @property
    def author(self):
        _session = object_session(self)
        return _session.query(User).get(self.author_id)

    @author.setter
    def author(self, user):
        if not isinstance(user, User):
            raise TypeError('user must be a instance of User')
        self.author_id = user.id

This code works well for simple operations. But it causes dirty queries making SQLAlchemy's features meaningless.

Code would be simple if it was configured via relationship() (e.g. query.filter(author=me)) got messed up(e.g. query.filter(author_id=me.id)).

Relationship(e.g. join) related features are never able to be used in query building.

Can I use property attribute, at least, in building query criterion(filter()/filter_by())?

Answer 1

you can still use relationship here. If you stick to "lazy loading", it will query for the related item in database B after loading the lead item in database A. You can place a ForeignKey() directive in the Column, even if there isn't a real one in the database. Or you can use primaryjoin directly:

class User(Base):
    __tablename__ = 'users'
    id = Column(BigInteger, id_seq, primary=True)
    name = Column(Unicode(256))


class Article(Base):
    __tablename__ = 'articles'
    __bind_key__ = 'another_engine'

    id = Column(BigInteger, id_seq, primary=True)
    author_id = Column(BigInteger, nullable=False, index=True)
    body = Column(UnicodeText, nullable=False)

    author = relationship("User", 
                primaryjoin="foreign(Article.author_id) == User.id")