通过 SQLAlchemy 中的链接对象键过滤查询 [英] Filter query by linked object key in SQLAlchemy

问题描述

从标题this来看是完全相同的问题，但是我看不出任何答案如何适用于我的用例:

Judging by the title this would be the exact same question, but I can't see how any of the answers are applicable to my use case:

我有两个类以及它们之间的关系:

I have two classes and a relationship between them:

treatment_association = Table('tr_association', Base.metadata,
    Column('chronic_treatments_id', Integer, ForeignKey('chronic_treatments.code')),
    Column('animals_id', Integer, ForeignKey('animals.id'))
)

class ChronicTreatment(Base):
    __tablename__ = "chronic_treatments"
    code = Column(String, primary_key=True)

class Animal(Base):
    __tablename__ = "animals"
    treatment = relationship("ChronicTreatment", secondary=treatment_association, backref="animals")

我希望能够仅选择经过代码为X"的治疗的动物.我尝试了很多方法.

I would like to be able to select only the animals which have undergon a treatment which has the code "X". I tried quite a few approaches.

这个失败了一个AttributeError:

sql_query = session.query(Animal.treatment).filter(Animal.treatment.code == "chrFlu")
for item in sql_query:
    pass

mystring = str(session.query(Animal))

这个很高兴返回一个未过滤的动物列表:

And this one happily returns a list of unfiltered animals:

sql_query = session.query(Animal.treatment).filter(ChronicTreatment.code == "chrFlu")
for item in sql_query:
    pass

mystring = str(session.query(Animal))

与我可以放在一起的上述线程中的示例最接近的内容:

The closest thing to the example from the aforementioned thread I could put together:

subq = session.query(Animal.id).subquery()
sql_query = session.query(ChronicTreatment).join((subq, subq.c.treatment_id=="chrFlu"))
for item in sql_query:
    pass

mystring = str(session.query(Animal))
mydf = pd.read_sql_query(mystring,engine)

也会因 AttributeError 而失败.

你能帮我整理这份清单吗?

Can you hel me sort this list?

推荐答案

首先，表定义有两个问题:

First, there are two issues with table definitions:

1) 在 treatment_association 你有 Integer 列指向 chronic_treatments.code 而 code 是 <代码>字符串列.

1) In the treatment_association you have Integer column pointing to chronic_treatments.code while the code is String column.

我认为在 chronic_treatments 中有一个整数 id 更好，这样你就不会在另一个表中复制字符串代码，也有机会添加稍后将有更多字段添加到chronic_treatments.更新:不完全正确，您仍然可以添加更多字段，但是如果您决定重命名它，更改代码"会更加复杂.

I think it's just better to have an integer id in the chronic_treatments, so you don't duplicate the string code in another table and also have a chance to add more fields to chronic_treatments later. Update: not exactly correct, you still can add more fields, but it will be more complex to change your 'code' if you decide to rename it.

2) 在Animal 模型中，您有一个名为treatment 的关系.这很令人困惑，因为您有多对多关系，它应该是复数 - treatments.

2) In the Animal model you have a relation named treatment. This is confusing because you have many-to-many relation, it should be plural - treatments.

解决以上两个问题后，您的查询为什么不起作用应该更清楚了.这个(我用 treatments 替换了 treatment:

After fixing the above two, it should be clearer why your queries did not work. This one (I replaced treatment with treatments:

sql_query = session.query(Animal.treatments).filter(
    Animal.treatments.code == "chrFlu")

Animal.treatments 表示多对多关系，它不是 SQL Alchemy 模式，因此您不能将其传递给 query 或使用在 filter 中.

The Animal.treatments represents a many-to-many relation, it is not an SQL Alchemy mode, so you can't pass it to the query nor use in a filter.

由于同样的原因，下一个无法工作(您将 Animal.treatments 传递到 query 中.

Next one can't work for the same reason (you pass Animal.treatments into the query.

最后一个更接近，您实际上需要 join 才能获得结果.

The last one is closer, you actually need join to get your results.

我认为将查询理解为 SQL 会更容易(而且您无论如何都需要了解 SQL 才能使用 sqlalchemy):

I think it is easier to understand the query as SQL (and you anyway need to know SQL to be able to use sqlalchemy):

animals = session.query(Animal).from_statement(text(
    """
       select distinct animals.* from animals
       left join tr_association assoc on assoc.animals_id = animals.id
       left join chronic_treatments on chronic_treatments.id = assoc.chronic_treatments_id
       where chronic_treatments.code = :code
 """)
).params(code='chrFlu')

它将选择animals并通过tr_association加入chronic_treatments，并通过代码过滤结果.

It will select animals and join chronic_treatments through the tr_association and filter the result by code.

有了这个，就可以很容易地使用 SQL-less 语法重写它:

Having this it is easy to rewrite it using SQL-less syntax:

sql_query = session.query(Animal).join(Animal.treatments).filter(
    ChronicTreatment.code == "chrFlu")

这将返回您想要的 - 使用给定代码进行相关慢性治疗的动物列表.

That will return what you want - a list of animals who have related chronic treatment with given code.

这篇关于通过 SQLAlchemy 中的链接对象键过滤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！