Building Custom Comparators

[jiansoung]

Building Custom Comparators

The hybrid_property.comparator() decorator introduced in this section replaces the use of the hybrid_property.expression() decorator. They cannot be used together.

The example class below allows case-insensitive comparisons on the attribute named word_insensitive:

from sqlalchemy.ext.hybrid import Comparator, hybrid_property
from sqlalchemy import func, Column, Integer, String
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class CaseInsensitiveComparator(Comparator):
    def __eq__(self, other):
        return func.lower(self.__clause_element__()) == func.lower(other)

class SearchWord(Base):
    __tablename__ = 'searchword'
    id = Column(Integer, primary_key=True)
    word = Column(String(255), nullable=False)

    @hybrid_property
    def word_insensitive(self):
        return self.word.lower()

    @word_insensitive.comparator
    def word_insensitive(cls):
        return CaseInsensitiveComparator(cls.word)

Above, SQL expressions against word_insensitive will apply the LOWER() SQL function to both sides:

>>> print Session().query(SearchWord).filter_by(word_insensitive="Trucks")
SELECT searchword.id AS searchword_id, searchword.word AS searchword_word
FROM searchword
WHERE lower(searchword.word) = lower(:lower_1)

The CaseInsensitiveComparator above implements part of the ColumnOperators interface. A “coercion” operation like lowercasing can be applied to all comparison operations (i.e. eq, lt, gt, etc.) using Operators.operate():

class CaseInsensitiveComparator(Comparator):
    def operate(self, op, other):
        return op(func.lower(self.__clause_element__()), func.lower(other))

References:

1. Building Custom Comparators