How to group by result value using Flask SQL Alchemy?

Question

I have a database with essentially two tables, a table of listings which is linked one to many to a reviews table.

class Listing(db.Model):
    __tablename__ = 'listings'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(500), index=True)

class Review(db.Model):
    __tablename__ = 'reviews'
    id = db.Column(db.Integer, primary_key=True)
    rating = db.Column(db.Integer())
    listing_id = db.Column(db.Integer, db.ForeignKey('listings.id'))
    listing = db.relationship("Listing", backref="reviews")

My goal is to get a summary of each listings 1 to 5 star reviews per listing. Basically something like this:

name, rating, count
Listing A, 5, 10
Listing A, 4, 3
Listing A, 3, 2
Listing B, 5, 6
Listing B, 2, 5

I thought I had it with this query but I am still missing the column Review.rating so whilst it is giving me the correct listing and each of the counts for each rating, it's not giving me the rating value so I have no idea what the actual rating the count is related to.

I think I am close but cannot figure it out.

listing_summary = db.session \
    .query(Listing.name, db.func.count(Review.review_id)) \
    .outerjoin(Review, Listing.id==Review.listing_id) \
    .order_by(Listing.name.asc()) \
    .group_by(Listing.name) \
    .group_by(Review.rating).all()

for listing in listing_summary:
    print(listing)

Current results:

('Listing A', 59)
('Listing A', 9)
('Listing A', 20)
('Listing A', 31)
('Listing A', 89)
('Listing B', 0)
('Listing C', 0)
('Listing D', 0)
('Listing E', 0)

I did think that grouping by listing.name then review.rating would give me a structure more like:

Listing A, [(5, 59),(4, 9),(3, 20),(2, 31),(1, 89)]

Answer 1

I believe that simply adding Review.rating to your query should do the trick:

listing_summary = db.session \
    .query(Listing.name, Review.rating, db.func.count(Review.review_id)) \
    .outerjoin(Review, Listing.id==Review.listing_id) \
    .order_by(Listing.name.asc()) \
    .group_by(Listing.name) \
    .group_by(Review.rating).all()