SQLAlchemy:具有关系的混合表达式 [英] SQLAlchemy: Hybrid expression with relationship
问题描述
我有两个具有简单一对多关系的Flask-SQLAlchemy模型，例如下面的最小示例:
I have two Flask-SQLAlchemy models with a simple one-to-many relationship, like the minimal example below:
class School(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
address = db.Column(db.String(30))
class Teacher(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
id_school = db.Column(db.Integer, db.ForeignKey(School.id))
school = relationship('School', backref='teachers')
然后，我向使用该关系的老师添加一个混合属性，如下所示:
Then I add an hybrid property to teacher that uses the relationship, like so:
@hybrid_property
def school_name(self):
return self.school.name
当我将其用作teacher_instance.school_name时，该属性可以正常工作.但是，我也想进行类似Teacher.query.filter(Teacher.school_name == 'x')的查询，但这给了我一个错误:
And that property works just fine when I use it as teacher_instance.school_name. However, I'd also like to make queries like Teacher.query.filter(Teacher.school_name == 'x'), but that gives me an error:
`AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'school_name'`.
在SQLAlchemy文档之后，我添加了一个简单的混合表达式，如下所示:
Following SQLAlchemy documentation, I added a simple hybrid expression, like the following:
@school_name.expression
def school_name(cls):
return School.name
但是，当我再次尝试相同的查询时，它会生成一个不带join子句的SQL查询，因此我获得了School中所有可用的行，而不仅仅是匹配Teacher中外键的行.
However, when I try the same query again, it generates an SQL query without the join clause, so I get all available rows in School, not only those matching the foreign key in Teacher.
从SQLAlchemy文档中，我意识到该表达式期望一个已经存在联接的上下文，因此我再次尝试查询该查询:
From SQLAlchemy documentation I realized that the expression expects a context where the join is already present, so I tried the query again as:
Teacher.query.join(School).filter(Teacher.school_name == 'x')
这确实有效，但是如果我需要了解School模型来获取语法糖，那么它一开始就无法达到将语法糖放入其中的目的.我希望有一种方法可以使该表达式加入表达式，但是我在任何地方都找不到它.文档中有一个示例，该表达式返回直接由select()构建的子查询，但即使那样对我也不起作用.
And that actually works, but it defeats the purpose of trying to get the syntactic sugar in there in the first place if I need knowledge of the School model to get that. I expect there's a way to get that join in the expression, but I couldn't find it anywhere. The documentation has an example with the expression returning a subquery built directly with the select(), but even that didn't work for me.
有什么想法吗?
更新
在下面的Eevee回答之后，我按建议使用了关联代理，并且可以正常工作，但是我也对它应该与select()子查询一起使用的评论感到好奇，并试图找出我做错了什么.我最初的尝试是:
After Eevee's answer below, I used the association proxy as suggested and it works, but I also got curious with the comment that it should work with the select() subquery and tried to figure out what I did wrong. My original attempt was:
@school_name.expression
def school_name(cls):
return select(School.name).where(cls.id_school == School.id).as_scalar()
事实证明，这给了我一个错误，因为我错过了select()中的列表.下面的代码可以正常工作:
And it turns out that was giving me an error because I had missed the list in select(). The code below works fine:
@school_name.expression
def school_name(cls):
return select([School.name]).where(cls.id_school == School.id).as_scalar()
推荐答案
A much simpler approach for a simple case like this is an association proxy:
class Teacher(db.Model):
school_name = associationproxy('school', 'name')
这支持自动查询(至少使用==).
This supports querying (at least with ==) automatically.
我很好奇混合型select()示例对您不起作用，因为这是在混合型中解决此问题的最简单方法.为了完整起见，您还可以使用 transformer 直接修改查询，而不是子查询.
I'm curious how the hybrid select() example didn't work for you, since that's the easiest way to fix this within a hybrid. And for the sake of completion, you could also use a transformer to amend the query directly rather than subquerying.
这篇关于SQLAlchemy:具有关系的混合表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
