python - SQLAlchemy - 带连接的复杂子查询

Question

我需要能够将其转换为 SQLALchemy，我有点新，但这是必需的。 SQL 查询是这样的:

SELECT name, 
(
    SELECT value from account_settings 
    WHERE name = "max_allowed_records" and accounts.id = account_settings.account_id
) AS max_allowed_records,
(
    SELECT count(*) from employees 
join employeelists on employees.employeelist_id = employeelists.id 
where employeelists.account_id = accounts.id 
group by employeelists.account_id
) AS RECORD_COUNT FROM accounts
having coalesce(max_allowed_records,0) < coalesce(RECORD_COUNT,0);

在连接形式中，我认为它们产生相同的结果(尝试这样以更好地在 SQLAlchemy 查询中可视化它):

SELECT accounts.name, account_settings.value AS max_allowed_records, count(employees.id) as RECORD_COUNT
FROM accounts
left join account_settings on account_settings.account_id = accounts.id and account_settings.name = "max_allowed_records"
left join employeelists on employeelists.account_id = accounts.id
left join employees on employees.employeelist_id = employeelists.id
group by accounts.id
having coalesce(account_settings.value,0) < coalesce(RECORD_COUNT,0)

所以我在这里想要的是，我应该从名称为 max_allowed_records 的帐户的帐户设置中获取值，然后将其与该帐户拥有的员工数量进行比较。不幸的是，帐户和员工之间的唯一链接是员工列表。我想知道你们是否可以指导我在这里需要做什么？ SQLAlchemy 加入了吗？或子查询？

表定义:

class Account(Base):
    __tablename__ = "accounts"

    id = synonym("raw_id")
    name = Column(String(255), nullable=False)
    settings = relationship("AccountSetting")

class AccountSetting(Base):
    __tablename__ = "account_settings"

    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey("accounts.id"))
    name = Column(String(30))
    value = Column(String(1000))

class EmployeeList(Base):
    __tablename__ = "employeelists"

    id = Column(Integer, primary_key=True)
    raw_id = Column("id", Integer, primary_key=True)
    name = Column(String(255))
    account_id = Column(Integer, ForeignKey("accounts.id"))

class Employee(Base):
    __tablename__ = "employees"

    id = synonym("raw_id")
    raw_id = Column("id", Integer, primary_key=True)
    employeelist_id = Column(Integer, ForeignKey("employeelists.id"), nullable=False)

Answer 1

经过整个周末的休息，我能够将 SQL 查询转换为 SQLAlchemy 查询:

    query = query.outerjoin(max_records_settings,\
            and_(max_records_settings.name == "max_allowed_records",\
                Account.id == max_records_settings.account_id))\
        .outerjoin(EmployeeList, Account.id == EmployeeList.account_id)\
        .add_columns(max_records_settings.value)\
        .join(Employee, EmployeeList.id == Employee.employeelist_id)\
        .group_by(Account.id)\
        .having(coalesce(func.count(Employee.id),0) > (coalesce(max_records_settings.value,0)))\