CODEWITHSUNDEEP
pythondjango-modelssqlalchemy
codeape
codeape

Reputation: 100766

SQLAlchemy equivalent of Django ORM's relationship-spanning filter

This example is from the Django documentation.

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()

In Django I can use:

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

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

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")

EDIT

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)

A few more questions:

  1. Are there other ways to accomplish this using SQLAlchemy than the ones above?
  2. Would it not make sense if you could do session.query(Entry).filter(Entry.blog.name == u"One blog") in many-to-one relationships?
  3. What SQL does Django's ORM produce in this case?

Upvotes: 4

Views: 3451

Answers (3)

Alexander Litvinenko
Alexander Litvinenko

Reputation: 319

I also always dreamed of having Django-like "magic joins". I'm familiar with sqlalchemy-django-query it, I found that it's not powerful enough for my tasks.

That's why I created https://github.com/absent1706/sqlalchemy-mixins#django-like-queries.

It works similar to sqlalchemy-django-query but has more additional features (here's a comparison). Also it's well tested and documented.

Upvotes: 2

kunl
kunl

Reputation: 1195

Already late to the party, but i stumbled across this:
https://github.com/mitsuhiko/sqlalchemy-django-query/blob/master/sqlalchemy_django_query.py

This tries to build queries using the django notation.

From the docs: Post.query.filter_by(pub_date__year=2008)

Upvotes: 1

Bogdan
Bogdan

Reputation: 8246

How about:

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

Upvotes: 1

Related Questions