Flask Admin, SQLAlchemy - Error When Searching a hybrid_property Field in a View

71 Views Asked by At

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?

0

There are 0 best solutions below