Filtering an object in SQLAlchemy

Question

I have a declared model in which a table stores the "source" path identifier of an object. Then I have @hybrid_property , which allows you to directly get and set the object that is identified by this field (which is not another declarative model). Is there a way to directly request this high level?

I can do it:

 session.query(Member).filter_by(program_raw=my_program.raw) 

I want to be able to do this:

 session.query(Member).filter_by(program=my_program) 

where my_program.raw == "path/to/a/program"

Member has a program_raw field and a program property that receives the correct program instance and sets the corresponding program_raw value. program has a simple raw field that uniquely identifies it. If necessary, I can provide more code.

The problem is that currently SQLAlchemy is simply trying to pass an instance of the program as a parameter to the query instead of the raw value. This results in Error binding parameter 0 - probably unsupported type. error Error binding parameter 0 - probably unsupported type. .

• Also, SQLAlchemy should know that when comparing a program it should use Member.program_raw and map this to the raw property of this parameter. Using this method Member.program_raw is done simply with @program.expression , but I can’t figure out how to correctly translate the program parameter (using Comparator?) And / or
• SQLAlchemy should know that when filtering using the program instance, it must use the raw attribute.

---

My use case may be a bit abstract, but imagine that I saved a serialized RGB value in a database and had a property with the Color class on the model. I want to filter by class Color, and should not deal with RGB values ​​in my filters. The color class has no problem telling me its RGB value.

Answer 1

It turned out by reading the source of relationship . The trick is to use a custom Comparator for a property that knows how to compare two things. In my case, it is as simple as:

 from sqlalchemy.ext.hybrid import Comparator, hybrid_property class ProgramComparator(Comparator): def __eq__(self, other): # Should check for case of `other is None` return self.__clause_element__() == other.raw class Member(Base): # ... program_raw = Column(String(80), index=True) @hybrid_property def program(self): return Program(self.program_raw) @program.comparator def program(cls): # program_raw becomes __clause_element__ in the Comparator. return ProgramComparator(cls.program_raw) @program.setter def program(self, value): self.program_raw = value.raw 

Note. In my case, Program('abc') == Program('abc') (I redefined __new__ ), so I can just return a “new” program all the time. In other cases, the instance should probably be lazily created and stored in the Member instance.