Filter by an object in SQLAlchemy

python, sqlalchemy

Solution

Figured it out by reading the source for `relationship`. The trick is to use a custom `Comparator` for the property, which knows how to compare two things. In my case it's 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've overridden `__new__`), so I can just return a "new" Program all the time. For other cases, the instance should probably be lazily created and stored in the Member instance.

Problem

I have a declared model where the table stores a "raw" path identifier of an object. I then have a `@hybrid_property` which allows directly getting and setting the object which is identified by this field (which is not another declarative model). Is there a way to query directly on this high level? I can do this: ``` 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 field `program_raw` and a property `program` which gets the correct `Program` instance and sets the appropriate `program_raw` value. `Program` has a simple `raw` field which identifies it uniquely. I can provide more code if necessary. The problem is that currently, SQLAlchemy simply tries to pass the program instance as a parameter to the query, instead of its `raw` value. This results in a `Error binding parameter 0 - probably unsupported type.` error. - Either, SQLAlchemy needs to know that when comparing the `program`, it must use `Member.program_raw` and match that against the `raw` property of the parameter. Getting it to use `Member.program_raw` is done simply using `@program.expression` but I can't figure out how to translate the `Program` parameter correctly (using a Comparator?), and/or - SQLAlchemy should know that when I filter by a `Program` instance, it should use the `raw` attribute. My use-case is perhaps a bit abstract, but imagine I stored a serialized RGB value in the database and had a property with a Color class on the model. I want to filter by the Color class, and not have to deal with RGB values in my filters. The color class has no problems telling me its RGB value.

Original source