backref lazy='dynamic' - does not support object population - eager loading cannot be applied

Question

How can I get Planes with Bookings between 2 dates specified. I need the Bookings to be filtered down to only be included if they're between 2 dates. I tried this but get the error: 'Plane.bookings' does not support object population - eager loading cannot be applied.

Why does this error occur and how can it be fixed?

planes = (db.session.query(Plane)
             .join(Booking)
             .filter(Booking.start_date >= date)
             .options(contains_eager(Plane.bookings)))

The models:

class Booking(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        start_date = db.Column(db.DateTime)
        end_date = db.Column(db.DateTime)

        person_id = db.Column(db.Integer, db.ForeignKey('person.id'))
        person = db.relationship('Person', foreign_keys=[person_id], backref=db.backref('bookings', lazy='dynamic'))

        instructor_id = db.Column(db.Integer, db.ForeignKey('person.id'))
        instructor = db.relationship('Person', foreign_keys=[instructor_id], backref=db.backref('instructor_bookings', lazy='dynamic'))

        plane_id = db.Column(db.Integer, db.ForeignKey('plane.id'))
        plane = db.relationship('Plane', backref=db.backref('bookings', lazy='dynamic'))

        def __init__(self, start_date, end_date, plane_id, instructor_id, person_id):
            self.start_date = start_date
            self.end_date = end_date
            self.plane_id = plane_id
            self.instructor_id = instructor_id
            self.person_id = person_id

        def __repr__(self):
            return '<Booking... %r>'

        def as_dict(self):
           return {c.name: getattr(self, c.name) for c in self.__table__.columns}


    class Plane(db.Model):
        id = db.Column(db.Integer, primary_key=True)
        name = db.Column(db.String(80))
        description = db.Column(db.String(120))

        def __init__(self, name, description):
            self.name = name
            self.description = description

        def __repr__(self):
            return '<Plane... %r>'

        def as_dict(self):
           return {c.name: getattr(self, c.name) for c in self.__table__.columns}

Answer 1

The error message tells you exactly what is "wrong": a lazy='dynamic' relationship can't be eager loaded. Dynamic relationships produce new queries, not collections (lists). It doesn't make sense to pre-load a dynamic relationship because the entire point of a dynamic relationship is to be able to construct other queries.

Use the dynamic relationship to filter the bookings when accessing them. This isn't very optimal as it requires a query per plane to get the bookings.

planes = Plane.query.join(Plane.bookings).filter(Booking.start_date >= date)
for plane in planes:
    bookings = plane.bookings.filter(Booking.start_date >= date)

More likely, you wanted a non-dynamic relationship. Simply remove lazy='dynamic' from the relationship's backref. If you pass contains_eager for a join that has been filtered, the resulting collection will only have the filtered items.

plane = db.relationship(lambda: Plane, backref='bookings')
# ...
planes = Plane.query.join(Plane.bookings
).filter(Booking.start_date >= date
).options(db.contains_eager(Plane.bookings))

Also note that there is nothing stopping you from defining two relationships to the same collection, one dynamic and one not, using whichever is needed for what you are doing at the time.