CODEWITHSUNDEEP
pythonsqlalchemy
Dobes Vandermeer
Dobes Vandermeer

Reputation: 8820

SQLAlchemy eager loading collection size / count

SQLAlchemy can eagerly load the contents of the collection if I specify the joinedload option. However, I have a case where I'm not actually interested in the contents of the collection, just the number of elements in it.

Is it possible to have SQLAlchemy fetch the size of a collection eagerly as part of a query?

For example, say I have a structure like (the real example is lengthy)

class Person:
  name = Column(String)
  avatarUrl = Column(String)
  comments = relation(Comment)

class Wall:
  Person for_whom

class Comment
  commenter = relation(Person)
  wall = relation(Wall)
  text = Column(String)

Now (abstractly again) if I'm getting a list of comments on a wall can I also get the total number of comments the commenter has posted?

session.query(Comment)
    .filter(Comment.wall == wall)
    .options(joinedload("commenter"))
    .options(joinedcount("commenter.comments")) # Here's the mysterious part
    .all()

Upvotes: 2

Views: 566

Answers (1)

rkhayrov
rkhayrov

Reputation: 10260

# alias comments table because it will appear twice in query
comments = aliased(Comment)
result = (session.query(Comment, func.count(comments.id))
    .filter(Comment.wall==wall)
    .join(Person) # we need to join person table explicitly
    .join(comments) # to reach comments table again in another join 
    .group_by(Comment.id)
     # populates relationship using explicitly joined table
    .options(contains_eager(Comment.commenter))
    .all())

Upvotes: 1

Related Questions