英文:
How to group by result value using Flask SQL Alchemy?
问题
你的目标是获取每个房源的1到5星评价的汇总数据，类似以下的结构：
名称, 评分, 计数
Listing A, 5, 10
Listing A, 4, 3
Listing A, 3, 2
Listing B, 5, 6
Listing B, 2, 5
你的查询接近正确，但缺少了Review.rating列。你可以尝试以下查询来获得所需的结果：
listing_summary = db.session \
.query(Listing.name, Review.rating, db.func.count()) \
.outerjoin(Review, Listing.id==Review.listing_id) \
.group_by(Listing.name, Review.rating) \
.order_by(Listing.name.asc(), Review.rating.asc()) \
.all()
这将按照房源名称和评分对结果进行分组，并按名称和评分升序排序。然后，你可以在查询结果中找到所需的数据。
英文:
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)]
答案1
得分: 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()
英文:
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()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论