SQLAlchemy/Elixir - 查询以检查多对多关系列表中实体的成员资格
我正在尝试构建一个 sqlalchemy 查询来获取 MIT 助理教授的所有教授的姓名列表。请注意,某门课程可以有多名助理教授。
我想做的大致相当于:
uni_mit = University.get_by(name='MIT')
s = select([Professor.name],
and_(Professor.in_(Course.assistants),
Course.university = uni_mit))
session.execute(s)
这不起作用,因为 in_
仅为实体的字段定义,而不是为整个实体定义。无法使用 Professor。 id.in_
as Course.assistants 是教授列表,而不是他们的 id 列表。我也尝试了 contains
但我也没有工作。
我的 Elixir 模型是:
class Course(Entity):
id = Field(Integer, primary_key=True)
assistants = ManyToMany('Professor', inverse='courses_assisted', ondelete='cascade')
university = ManyToOne('University')
..
class Professor(Entity):
id = Field(Integer, primary_key=True)
name = Field(String(50), required=True)
courses_assisted = ManyToMany('Course', inverse='assistants', ondelete='cascade')
..
如果我可以访问中间多对多实体,这将是微不足道的(条件为 and_(interm_table.prof_id = Professor.id, interm_table.course = Course.id)
,但 SQLAlchemy 显然对我隐藏了这个表,
我正在使用 Elixir 0.7 和 SQLAlchemy 0.6
顺便说一句:这个问题与 Sqlalchemy+elixir:如何查询多对多关系? 因为我需要检查教授是否满足所有满足条件的课程,而不是单个静态课程。
I am trying to construct a sqlalchemy query to get the list of names of all professors who are assistants professors on MIT. Note that there can be multiple assistant professors associated with a certain course.
What I'm trying to do is roughly equivalent to:
uni_mit = University.get_by(name='MIT')
s = select([Professor.name],
and_(Professor.in_(Course.assistants),
Course.university = uni_mit))
session.execute(s)
This won't work, because in_
is only defined for entity's fields, not for the whole entity.. Can't use Professor.id.in_
as Course.assistants is a list of Professors, not a list of their ids. I also tried contains
but I didn't work either.
My Elixir model is:
class Course(Entity):
id = Field(Integer, primary_key=True)
assistants = ManyToMany('Professor', inverse='courses_assisted', ondelete='cascade')
university = ManyToOne('University')
..
class Professor(Entity):
id = Field(Integer, primary_key=True)
name = Field(String(50), required=True)
courses_assisted = ManyToMany('Course', inverse='assistants', ondelete='cascade')
..
This would be trivial if I could access the intermediate many-to-many entity (the condition would be and_(interm_table.prof_id = Professor.id, interm_table.course = Course.id)
, but SQLAlchemy apparently hides this table from me.
I'm using Elixir 0.7 and SQLAlchemy 0.6.
Btw: This question is different from Sqlalchemy+elixir: How query with a ManyToMany relationship? in that I need to check the professors against all courses which satisfy a condition, not a single, static one.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以找到 Elixir 将其隐藏的中间表,但请注意,它使用完全限定的列名称(例如
__package_path_with_underscores__course_id
)。为了避免这种情况,请使用eg定义ManyToMany,然后您可以使用访问中间表
,并可以使用
table.c.prof_id
等访问列。更新:当然您可以在更高级别执行此操作,但不能在单个查询中执行此操作,因为 SQLAlchemy 尚不支持关系的
in_
。例如,对于两个查询:或者,或者:
第一步创建助理列表的列表。第二步压平列表列表并通过创建集合来删除重复项。
You can find the intermediate table where Elixir has hidden it away, but note that it uses fully qualified column names (such as
__package_path_with_underscores__course_id
). To avoid this, define your ManyToMany using e.g.and then you can access the intermediate table using
and can access the columns using
table.c.prof_id
, etc.Update: Of course you can do this at a higher level, but not in a single query, because SQLAlchemy doesn't yet support
in_
for relationships. For example, with two queries:Or, alternatively:
The first step creates a list of lists of assistants. The second step flattens the list of lists and removes duplicates through making a set.