'SqlAlchemy dynamic loading of related entities from query

The use case is pretty simple: I have 3 cascading entities

class Customer():
    users = relationship(
     'User',
      backref=backref('customer', lazy="subquery"),
      cascade=DbConstants.RELATIONSHIP_CASCADE_ALL_DELETE)


class User():
    reports = relationship('Report',
    backref=backref('user', lazy="subquery"),
    lazy="subquery",
    cascade=DbConstants.RELATIONSHIP_CASCADE_ALL_DELETE)


class Report():
    date_time_start = Column(DateTime())
    date_time_end = Column(DateTime())

I want to get all these entities in one query, but i want to filter the reports by their date.

customers = session.query(Customer).join(
         User, Customer.users, isouter=True
     ).join(
            Report,
            # this is where the reports should be filtered
            and_(Report.user_id == User.id, Report.date_time_start > date_start, Report.date_time_start < date_end),
     ).all()

From this I get the expected entity tree:

[
   customers:
      users: [ reports: [] ]
]

Except i get ALL the reports of the user in the array, no matter the start date. This means if I check the result like customers[0].users[0].reports, all the reports belonging to this user will be output. Is there a way so the reports attribute is only populated with the rows from the query ?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source