How do I fetch top courses that have at least two reviews?

Courses
1

I’m using Flask-SQLAlchemy with PostgreSQL. My CourseModel and ReviewModel manage courses and reviews. I want to list courses with 2+ reviews, but the query fails in production.

class CourseModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120))
    details = db.Column(db.Text)
    reviews = db.relationship('ReviewModel', backref='course', lazy='dynamic')

class ReviewModel(db.Model):
    __table_args__ = (db.UniqueConstraint('course_id', 'user_id'), {})
    id = db.Column(db.Integer, primary_key=True)
    submitted_on = db.Column(db.DateTime)
    remark = db.Column(db.Text)
    score = db.Column(db.SmallInteger)
    course_id = db.Column(db.Integer, db.ForeignKey('course_model.id'))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

tops = db.session.query(ReviewModel, func.count(ReviewModel.course_id))\
      .group_by(ReviewModel.course_id)\
      .having(func.count(ReviewModel.course_id) >= 2)\
      .order_by(func.count(ReviewModel.course_id).desc()).all()
2

hey, try joining CourseModel before grouping. sometimes forgetting the join messes up group counts in prod. rough experiments showed it fixed my issue. good luck!

3

I encountered a similar problem where the grouping didn’t consider the course relation properly. I solved it by filtering the courses based on an aggregate count in a subquery. Essentially, I first created a subquery that calculated the number of reviews per course, then joined it back to the main CourseModel query. This method ensured all course-related columns were appropriately handled by SQLAlchemy. It turned out that explicitly structuring the join and group by clauses avoided unexpected behavior in production upon deployment.