python - 使用混合属性计算 previous sibling 姐妹

Question

我有一个模型 FollowUp，其中包含与用户和项目关联的日期和描述:

 class FollowUp(Base):
    __tablename__ = 'followup'
    id =  Column(UUID(), primary_key=True)
    project_id = Column(UUID(), ForeignKey("project.id"))
    user_id = Column(UUID(), ForeignKey("user.id"))

    followup_date = Column(DateTime())
    description= Column(Unicode())

我想使用一个混合属性来返回一个子查询，该子查询计算在当前跟进之前发生的跟进的数量，该跟进与在项目中工作的特定用户相关联。

@hybrid_property
def previous_count(self):
    return session.query(func.count(FollowUps) \
        .filter(self.project_id == FollowUp.project_id ) \
        .filter(self.user_id == FollowUp.user_id) \
        .filter(self.followup_date > FollowUp.followup_date) \
        .as_scalar()

我会在类似于以下的查询中使用它:

session.query(Project.title, User.name, FollowUp.previous_count) \
    .filter(Project.id == @SOME_PROJECT_ID)

问题是 hybrid_property 没有使用返回的主查询:

SELECT
    count(:param_2) AS count_1   
FROM
    followup
WHERE
    AND followup.project_id = followup.project_id 
    AND followup.user_id = followup.user_id
    AND followup.followup_date < followup.followup_date

我应该更改什么才能使其正常工作？我试图创建一个别名:

@hybrid_property
def previous_count(self):
    alias_followup = aliased(FollowUp)
    return session.query(func.count(alias_followup)) \
        .join(alias_followup) \
        .filter(self.project_id == alias_followup.project_id ) \
        .filter(self.user_id == alias_followup.user_id) \
        .filter(self.followup_date > alias_followup.followup_date) \
        .as_scalar()

它给出了这个错误:

NoInspectionAvailable: No inspection system is available for object of type <type 'NoneType'>

我发现错误可能是由第一列为空引起的。其中隐藏了以下错误:

InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <AliasedClass at 0x7fde5c0d9e50; Suivi>, but got: Can't find any foreign key relationships between 'followup' and 'previous_count'.

出于测试目的，我用另一列删除了 func.count，并将连接子句修改为:

.join(alias_followup , self.id == FollowUp.id)

但它给了我另一个错误:

InvalidRequestError: Can't construct a join from <AliasedClass at 0x7f04ec7b6c90; FollowUp> to <AliasedClass at 0x7f04ec7b6c90; FollowUp>, they are the same entity

Answer 1

您使用别名是正确的，但没有正确使用它。另外，根据我对您的回答 other question ，混合属性需要两个部分。

@hybrid_property
def previous_count(self):
    # the python property, queries the database and returns a count
    cls = self.__class__
    return session.query(func.count(cls.id)).filter(cls.project == self.project, cls.ts < self.ts).scalar()

@previous_count.expression
def previous_count(cls):
    # the sql property, constructs a scalar subquery with an alias
    other = aliased(cls)
    return session.query(func.count(other.id)).filter(other.project_id == cls.project_id, other.ts < cls.ts).as_scalar()

下面是一个简单的工作示例:

from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, DateTime, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import Session, relationship, aliased

engine = create_engine('sqlite:///', echo=True)
session = Session(bind=engine)
Base = declarative_base(bind=engine)


class Project(Base):
    __tablename__ = 'project'

    id = Column(Integer, primary_key=True)


class Followup(Base):
    __tablename__ = 'followup'

    id = Column(Integer, primary_key=True)
    project_id = Column(Integer, ForeignKey(Project.id))
    ts = Column(DateTime, nullable=False)

    project = relationship(Project, backref='followups')

    @hybrid_property
    def previous_count(self):
        # the python property, queries the database and returns a count
        cls = self.__class__
        return session.query(func.count(cls.id)).filter(cls.project == self.project, cls.ts < self.ts).scalar()

    @previous_count.expression
    def previous_count(cls):
        # the sql property, constructs a scalar subquery with an alias
        other = aliased(cls)
        return session.query(func.count(other.id)).filter(other.project_id == cls.project_id, other.ts < cls.ts).as_scalar()


Base.metadata.create_all()

p1 = Project()
p2 = Project()

f1 = Followup(project=p1, ts=datetime(2014, 1, 1))
f2 = Followup(project=p1, ts=datetime(2014, 2, 1))
f3 = Followup(project=p1, ts=datetime(2014, 3, 1))
f4 = Followup(project=p1, ts=datetime(2014, 4, 1))
f5 = Followup(project=p2, ts=datetime(2014, 5, 1))

session.add_all((p1, p2))
session.commit()

print(session.query(Followup.id, Followup.previous_count).all())

创建了两个项目，一个有 4 个跟进，另一个有 1 个。然后查询 followups 以打印他们的 id 和以前的计数。生成以下查询:

SELECT followup.id AS followup_id, (SELECT count(followup_1.id) AS count_1 
FROM followup AS followup_1 
WHERE followup_1.project_id = followup.project_id AND followup_1.ts < followup.ts) AS anon_1 
FROM followup

输出是:

[(1, 0), (2, 1), (3, 2), (4, 3), (5, 0)]