从至少有2次复习的课程开始，选择大多数复习的课程

Question

我在PostgreSQL中使用的是烧瓶-SQLAlchemy。我有以下两种模式：

class Course(db.Model):
    id = db.Column(db.Integer, primary_key = True )
    course_name =db.Column(db.String(120))
    course_description = db.Column(db.Text)
    course_reviews = db.relationship('Review', backref ='course', lazy ='dynamic')

class Review(db.Model):
    __table_args__ = ( db.UniqueConstraint('course_id', 'user_id'), { } )
    id = db.Column(db.Integer, primary_key = True )
    review_date = db.Column(db.DateTime)#default=db.func.now()
    review_comment = db.Column(db.Text)
    rating = db.Column(db.SmallInteger)
    course_id = db.Column(db.Integer, db.ForeignKey('course.id') )
    user_id = db.Column(db.Integer, db.ForeignKey('user.id') )

我想从至少两个评论开始，选择那些复习最多的课程。以下SQLAlchemy查询在SQlite中运行良好：

most_rated_courses = db.session.query(models.Review, func.count(models.Review.course_id)).group_by(models.Review.course_id).\
          having(func.count(models.Review.course_id) >1) \   .order_by(func.count(models.Review.course_id).desc()).all()

但是，当我在生产中切换到PostgreSQL时，它会给出以下错误：

ProgrammingError: (ProgrammingError) column "review.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT review.id AS review_id, review.review_date AS review_...
               ^
 'SELECT review.id AS review_id, review.review_date AS review_review_date, review.review_comment AS review_review_comment, review.rating AS review_rating, review.course_id AS review_course_id, review.user_id AS review_user_id, count(review.course_id) AS count_1 \nFROM review GROUP BY review.course_id \nHAVING count(review.course_id) > %(count_2)s ORDER BY count(review.course_id) DESC' {'count_2': 1}

我试图通过在GROUP子句中添加models.Review来修复查询，但它没有工作：

most_rated_courses = db.session.query(models.Review, func.count(models.Review.course_id)).group_by(models.Review.course_id).\
          having(func.count(models.Review.course_id) >1) \.order_by(func.count(models.Review.course_id).desc()).all()

有人能帮我解决这个问题吗。非常感谢

Answer 1

SQLite和MySQL都允许具有聚合(如count())的查询，而不将group BY应用于所有其他列--就标准SQL而言，这是无效的，因为如果聚合组中存在多个行，则必须选择返回的第一个行，这基本上是随机的。

因此，对Review的查询基本上返回给您的是每个不同的课程id的第一个“审查”行--就像课程id 3一样，如果您有七个“评论”行，那么它只是在"course_id=3“组中选择一个本质上随机的”评论“行。我收集到你真正想要的答案，“课程”，因为你可以选择半随机选择的评论对象，只需在它上调用".course“，给出正确的路线，但这是一个反向的方法。

但是，一旦使用了适当的数据库(如Postgresql )，就需要使用正确的SQL。从“审查”表中需要的数据只是course_id和计数，没有其他的，所以只需要查询它(首先假设我们实际上不需要显示计数，这是立即的)：

most_rated_course_ids = session.query(
                        Review.course_id,
                    ).\
                    group_by(Review.course_id).\
                    having(func.count(Review.course_id) > 1).\
                    order_by(func.count(Review.course_id).desc()).\
                    all()

但这不是您的课程对象-您想要获取it列表并将其应用到课程表中。我们首先需要将课程is列表保留为SQL结构，而不是加载数据--也就是说，通过将查询转换为子查询(将单词.all()改为.subquery())，将其转换为派生表：

most_rated_course_id_subquery = session.query(
                    Review.course_id,
                ).\
                group_by(Review.course_id).\
                having(func.count(Review.course_id) > 1).\
                order_by(func.count(Review.course_id).desc()).\
                subquery()

链接到课程的一个简单方法是使用IN：

 courses = session.query(Course).filter(
       Course.id.in_(most_rated_course_id_subquery)).all()

但这实际上是要抛弃你正在寻找的“订单”，也没有给我们提供任何好的方式来报告这些计数和课程的结果。我们需要把这一点与我们的课程一起进行，这样我们才能报告它，并按它来订购。为此，我们使用从"course“表到派生表的联接。SQLAlchemy很聪明，如果我们只调用join()，就知道可以加入"course_id“外键。

courses = session.query(Course).join(most_rated_course_id_subquery).all()

然后，为了获得计数，我们需要将它添加到我们的子查询返回的列以及一个标签，以便我们可以引用它：

most_rated_course_id_subquery = session.query(
                        Review.course_id,
                        func.count(Review.course_id).label("count")
                    ).\
                    group_by(Review.course_id).\
                    having(func.count(Review.course_id) > 1).\
                    subquery()

courses = session.query(
                Course, most_rated_course_id_subquery.c.count
            ).join(
                most_rated_course_id_subquery
            ).order_by(
                most_rated_course_id_subquery.c.count.desc()
            ).all()

我喜欢向人们指出关于GROUP的一篇很棒的文章，这种查询是SQL按技术分组，它指出了"select从A、联接到(带有聚合/组BY的子查询)“模式的共同需要。