SQLAlchemy conversion of Django annotate

Question

I have the following annotation in a Django model manager I'd like to convert to a SQLAlchemy ORM query:

annotations = {
        'review_count' : Count("cookbookreview", distinct=True), 
        'rating' : Avg("cookbookreview__rating")
    }
return self.model.objects.annotate(**annotations)

What I essentially need is each model object in the query to have review_count and rating attached to them as part of the initial query. I believe I can use column_property, but I would like to avoid this type of "calculated property" on the object, because I don't want the property (expensive lookup) being done for each object when I access the property in a template.

What is the right way to approach this problem? Thanks in advance.

Answer 1

So, for the sake of completeness and usefulness for others with this issue I present the following solution (which may or may not be the optimal way to solve this)

sq_reviews = db_session.query(CookbookReview.cookbook_id, 
    func.avg(CookbookReview.rating).label('rating'),\
    func.count('*').label('review_count')).\
    group_by(CookbookReview.cookbook_id).subquery()

object_list = db_session.query(
    Cookbook, sq_reviews.c.rating, sq_reviews.c.review_count).\
    outerjoin(sq_reviews, Cookbook.id==sq_reviews.c.cookbook_id).\
    order_by(Cookbook.name).limit(20)

The key here is the concept of SQLAlchemy subqueries. If you think of each annotation in my original Django query as a subquery, the concept is easier to understand. It's also worth noting that this query is quite speedy - many orders of magnitude swifter than it's (more concise/magical) Django counterpart. Hopefully this helps others curious about this particular Django/SQLAlchemy query analog.

Also keep in mind that you need to perform the actual annotation of the ORM objects yourself. A simple function like this called before sending the object list to your template will suffice:

def process(query):
    for obj, rating, review_count in query:
        obj.rating = rating
        obj.review_count = review_count
        yield obj