如何在where子句中动态选择不同类型的列？

Question

我在sqlalchemy有一个下表：

class FieldType(enum.Enum):
   INT_FIELD = 0
   FLOAT_FIELD = 1
   STRING_FIELD = 2

class EAVTable(Base):
     __tablename__ = 'EAVTable'

     field_name = Column(Stirng, primary_key=True)
     field_type = Column(Enum(FieldType))
     int_field = Column(Integer)
     float_field = Column(Float)
     string_field = Column(String)

这是对EAV model进行建模，以符合我的业务目的。你知道吗

现在为了在代码中方便地使用它，我有下面的hybrid_property。你知道吗

@hybrid_propderty
def value(self):
    if self.field_type == FieldType.INT_FIELD:
         return self.int_field
    ...

@value.setter
def value(self, value):
    if type(value) == int:
        self.field_type = FieldType.INT_FIELD
        self.int_field = value
    ...

当我尝试在Python代码中获取和设置字段时，这可以很好地工作。但我还是有个问题：

session.query(EAVTable).filter(EAVTable.value == 123) 

这不是现成的，但我有一个使用的想法杂交表达我们使用案例陈述：

@value.expression
def value(cls):
    return case(
        [
            (cls.field_type == FieldType.INT_FIELD, cls.int_field),
            (cls.field_type == FieldType.FLOAT_FIELD, cls.float_field),
            ...
        ]
    )

这在理论上是可行的，例如，为查询session.query(EAVTable.value = 123生成的SQL如下所示：

select * from where case 
    when field_type = INT_FIELD then int_field
    when field_type = FLOAT_FIELD then float_field
    when field_type = STRING_FIELD then string_field 
    end = 123;

这在语义上看起来像我喜欢的，但是后来我发现case表达式要求所有的case都具有相同的类型，或者它们被转换成相同的类型。你知道吗

我知道这是SQL语言的要求，与sqlachemy无关，但是对于经验丰富的sqlalchemy用户来说，有什么简单的方法可以实现我想要的目标吗？有没有办法绕过这个限制？你知道吗

Answer 1

可以使用custom comparator将比较移到CASE表达式中：

from sqlalchemy.ext.hybrid import Comparator

class PolymorphicComparator(Comparator):
    def __init__(self, cls):
        self.cls = cls

    def __clause_element__(self):
        # Since SQL doesn't allow polymorphism here, don't bother trying.
        raise NotImplementedError(
            f"{type(self).__name__} cannot be used as a clause")

    def operate(self, op, other):
        cls = self.cls
        return case(
            [
                (cls.field_type == field_type, op(field, other))
                for field_type, field in [
                    (FieldType.INT_FIELD, cls.int_field),
                    (FieldType.FLOAT_FIELD, cls.float_field),
                    (FieldType.STRING_FIELD, cls.string_field),
                ]
            ],
            else_=False
        )

class EAVTable(Base):
     ...

     # This replaces @value.expression
     @value.comparator
     def value(cls):
         return PolymorphicComparator(cls)

这样，公共类型就是布尔类型。你知道吗