SQLAlchemy等效于Django ORM的关系扩展过滤器 [英] SQLAlchemy equivalent of Django ORM's relationship-spanning filter

问题描述

此示例来自 Django文档。

给出（Django）数据库模型：

Given the (Django) database model:

class Blog(models.Model):
    name = models.CharField(max_length=100)

class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()

在Django中我可以使用：

In Django I can use:

Entry.objects.filter(blog__name__exact='Beatles Blog')

获取具有指定名称的博客的所有 Entry 对象。

to get all Entry objects for blogs with the specified name.

问题：给定以下模型定义，等效的SQLAlchemy语句是什么？

Question: What is the equivalent SQLAlchemy statement, given the model definition below?

class Blog(Base):
    __tablename__ = "blog"
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(100))

class Entry(Base):
    __tablename__ = "entry"
    id = Column(Integer, primary_key=True)
    blogid = Column(Integer, ForeignKey(Blog.id))
    headline = Column(Unicode(255))
    body_text = Column(UnicodeText)

    blog = relationship(Blog, backref="entries")

编辑

我相信有两种方法可以完成此操作：

I believe there are two ways to accomplish this:

>>> q = session.query
>>> print q(Entry).join(Blog).filter(Blog.name == u"One blog")
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text 
FROM entry JOIN blog ON blog.id = entry.blogid 
WHERE blog.name = ?

>>> print q(Entry).filter(Entry.blog.has(Blog.name == u"One blog"))
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text 
FROM entry 
WHERE EXISTS (SELECT 1 
FROM blog 
WHERE blog.id = entry.blogid AND blog.name = ?)

# ... and of course
>>> blog = q(Blog).filter(Blog.name == u"One blog")
>>> q(Entry).filter(Entry.blog == blog)

还有几个问题：

1. 除了上述方法以外，还有其他方法可以使用SQLAlchemy完成此操作吗？


2. 是否没有道理是否可以在多对一关系中执行 session.query（Entry）.filter（Entry.blog.name == u一个博客）？


3. 在这种情况下Django的ORM生成什么SQL？

推荐答案

怎么样：

session.query(model.Entry).join((model.Blog, model.Entry.blogid==model.Blog.id)).filter(model.Blog.name=='Beatles Blog').all()

这篇关于SQLAlchemy等效于Django ORM的关系扩展过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！