I am using SQLAlchemy with flask_admin and I am trying to search a field with a many-to-one relationship with my root object. Let's say I want to know the starting dates of the employees who work at the dealership where my car was sold. I want to be able to search those starting dates in my Car view.
For example, let's say I have the following data setup:
| CAR | |
|---|---|
| VIN | Primary Key |
| dealershipSoldAtId | Foreign Key (dealership.id) [pretend this table exists] |
| ... | ... |
| Employee | |
|---|---|
| id | Primary Key |
| dealershipWorksAtId | Foreign Key (dealership.id) |
| employeeStartDate | String (for simplicity instead of DateTime) |
Now I have my Car SQLAlchemy class:
from sqlalchemy.ext.hybrid import hybrid_property
class Car(...):
__tablename__ = "car"
VIN = Column(...)
dealership_sold_at_id = Column(..., ForeignKey("dealership.id"))
@hybrid_property
def employee_start_dates(self):
start_dates = db_session.query(Employee.start_date).filter_by(
Employee.dealership_works_at_id == self.dealership_sold_at_id
).all()
return ", ".join(start_dates)
Then in my view, I have:
class CarView(...):
column_list = ("VIN", "dealership_sold_at_id", "employee_start_dates")
column_searchable_list = ("employee_start_dates",)
column_labels = {
"dealership_sold_at_id": "Dealership Sold At",
"employee_start_dates": "Employee Start Dates"
}
...
When I load the view, I see the employee_start_dates just fine. But when I try to search them, I get the error:
When interpreting attribute "Car.employee_start_dates" as a SQL expression, expected __clause_element__() to return a ClauseElement object, got: \'2023-01-01, 2023-03-04, 2023-04-21\'
How exactly should I go about doing this?