Filtering a model on a related model still shows the "filtered out" related objects

Question

A SportsClass has many Courses. I want to be able to only get the courses that are available.

I tried the following:

sports_classes = db.session.query(SportsClass).join(Course).filter((SportsClass.description.contains(query))|(SportsClass.name.contains(query)))
sports_classes = sports_classes.filter((Course.bookable == "bookable") | (Course.bookable == "waitingList"))

However, the result returned by that query are (converted to json and simplified):

[
"description": "description",
    "courses": [
      {
        "name": "Name",
        "bookable": "waitingList",
      },
      {
        "name": "Keine Angaben",
        "bookable": "canceled",
      }
    ],
  }
]

Why is the canceled course also in the result set? How can I get just the available courses for each class?

score 3 · Accepted Answer · answered Nov 14 '14 at 15:16

You are confusing joining/filtering with loading related objects. Your current query is filtering SportsClass to make sure at least one of its courses is 'bookable' or 'waitingList'. However, this doesn't effect what gets loaded by the relationship, that's handled separately from loading the query results.

Instead, you probably want a second relationship that only shows the not-canceled classes. Then put the contents of that relationship in the JSON output, rather than the "full" relationship.

class SportsClass(db.Model):
    # ...

class Course(db.Model):
    # ...
    sports_class_id = db.Column(db.Integer, db.ForeignKey(SportsClass.id), nullable=False)
    sports_class = db.relationship(SportsClass, backref='courses')

SportsClass.available_courses = db.relationship(
    Course, primaryjoin=db.and_(
        SportsClass.id == Course.sports_class_id,
        Course.bookable != 'canceled'
    ), viewonly=True
)

(I'm guessing at your model definitions, but the key part is to define and use that extra relationship to Course at the end.)

Filtering a model on a related model still shows the "filtered out" related objects

1 Answers1