'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 |
|---|
