SQLAlchemy - 在自定义 primaryjoin 关系中使用 'aliased' 查询

Question

我在我的Flask应用中使用SQLAlchemy（0.9.4）。应用里有两个表支持软删除。

class A(SoftDeleteMixin, db.Model):
    id = db.Column(db.BigInteger, primary_key=True)

    b_id = db.Column(db.BigInteger, db.ForeignKey('b.id'), nullable=False)
    b = soft_delete_relationship('B.id', 'A.b_id')

class B(SoftDeleteMixin, db.Model):
    id = db.Column(db.BigInteger, primary_key=True)
    parent_id = db.Column(db.BigInteger, db.ForeignKey('b.id'))

    parent = soft_delete_relationship(remote(id), parent_id, 'B.id', 'B.parent_id')
    children = soft_delete_relationship(remote(parent_id), id, 'B.parent_id', 'B.id')

SoftDeleteMixin是基于LimitingQuery的，具体可以参考这个链接：https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery

from sqlalchemy.orm.query import Query


class NonDeletedQuery(Query):
    def get(self, ident):
        return Query.get(self.populate_existing(), ident)

    def __iter__(self):
        return Query.__iter__(self.private())

    def from_self(self, *ent):
        return Query.from_self(self.private(), *ent)

    def private(self):
        mzero = self._mapper_zero()
        if mzero is not None and hasattr(mzero, 'class_'):
            soft_deleted = getattr(mzero.class_, 'soft_deleted', None)
            return self.enable_assertions(False).filter(soft_deleted.is_(False)) if soft_deleted else self
        else:
            return self

而soft_delete_relationship是用来建立关系的，它有一个自定义的主连接条件（用于连接那些没有被软删除的记录）。

def soft_delete_relationship(first, second, *args, **kwargs):
    if isinstance(first, str) and isinstance(second, str):
        other, other_column = first.split('.')
        _this, this_column = second.split('.')

        primaryjoin = ' & '.join(['({} == {})'.format(first, second), '{}.soft_deleted.is_(False)'.format(other)])

    else:
        other, other_column = args[0].split('.')
        _this, this_column = args[1].split('.')

        primaryjoin = lambda: (first == second) & getattr(second.table.c, 'soft_deleted').is_(False)

    kwargs['primaryjoin'] = primaryjoin
    return relationship(other, **kwargs)

问题出现在我写带有别名B的查询时：

b_parent = aliased(B)
A.query.join(A.b).outerjoin(b_parent, B.parent)

我得到的SQL是：

SELECT ... FROM a JOIN b ON b.id = a.b_id LEFT OUTER JOIN b AS b_1 ON b_1.id = b.parent_id AND *b*.soft_deleted IS False

但我期望得到的是：

SELECT ... FROM a JOIN b ON b.id = a.b_id LEFT OUTER JOIN b AS b_1 ON b_1.id = b.parent_id AND *b_1*.soft_deleted IS False

当我明确写出时：

A.query.join(A.b).outerjoin(b_parent, (b_parent.id == B.parent_id) & b_parent.soft_deleted.is_(False))

我得到了正确的查询。

我该如何在查询中获得正确的别名b_1，而不需要明确的连接条件呢？顺便提一下，在SQLAlchemy 0.7.9中是可以得到预期的SQL的。

sqlalchemy primaryjoin flask database querying soft delete relationship management aliased query limitingquery

SQLAlchemy - 在自定义 primaryjoin 关系中使用 'aliased' 查询

1 个回答

撰写回答