Convert SQLAlchemy Django Annotations

Question

I have the following annotation in the Django model manager, which I would like to convert to SQLAlchemy ORM query:

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

In fact, each model object in the request has review_count and rating attached to them as part of the original request. I believe that I can use column_property , but I would like to avoid this type of "calculated property" of the object, because I do not want the property (expensive search) to be performed for each object when I access the property in the template.

What is the correct way to solve this problem? Thanks in advance.

Answer 1

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

 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 point here is the concept of SQLAlchemy subqueries . If you think of each annotation in my original Django query as a subquery, the concept is clear. It is also worth noting that this request is quite fast - several orders of magnitude faster than it (shorter / magic), Django. Hope this helps others find out about this particular Django / SQLAlchemy query analogue.

Also keep in mind that you need to annotate the ORM objects themselves. A fairly simple function like this before sending a list of objects to your template:

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