SQLAlchemy - 在具有自定义主联接关系的查询中使用“别名" [英] SQLAlchemy - Using 'aliased' in query with custom primaryjoin relationship

问题描述

我在 Flask 应用程序中使用 SQLAlchemy (0.9.4).应用中有两个支持软删除的表.

I'm using SQLAlchemy (0.9.4) in my Flask application. There are two tables with soft delete support in application.

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)

SoftDeleteMixin is based on 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 构建与自定义primaryjoin 的关系(用于非soft_deleted 上的join).

And soft_delete_relationship constructs relationship with custom primaryjoin (for join on non-soft_deleted).

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 编写查询时出现问题:

The problem occurs when I write query with aliased 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.

How can I get proper alias to b_1 in query without explicit join condition? Btw, there was expected SQL in SQLAlchemy 0.7.9.

推荐答案

好的，我想通了.

getattr(second.table.c, 'soft_deleted') 也必须带有 remote() 注释.

getattr(second.table.c, 'soft_deleted') must be also with remote() annotation.

换句话说，B.parent 中 relationship 的 primaryjoin 应该是这样的:

In other words primaryjoin of relationship in B.parent should look like:

(remote(B.id) == B.parent_id) &远程(B.soft_deleted).is_(False)

(remote(B.id) == B.parent_id) & remote(B.soft_deleted).is_(False)

这篇关于SQLAlchemy - 在具有自定义主联接关系的查询中使用“别名"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！