How to select columns of different type in SQLAlchemy in the where clause?

[bobfang1992]

Hi, first I want to say sorry if this is not the right place to ask this question. I have a following table in 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)

This is to model the EAV model which fits my business purpose.

Now to use it easily in the code I have the following 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
    ...

This works fine when I try to get and set the fields in Python code. But I still have a problem:

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

This does not work out of the box but I had an idea of using hybrid.expression where we use a case statement:

@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),
            ...
        ]
    )

This in theory works, for example, the SQL generated for query session.query(EAVTable.value = 123 looks like:

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;

Which semantically looks like what I like, but later I find that the case expression requires all the cases have the same type, or they are cast into the same type.

I understand this is a requirement from the SQL language and has nothing to do with sqlachemy, but for more seasoned sqlalchemy user, is there any easy way to do what I want to achieve? Is there a way to walk around this constraint?

[zzzeek]

Hi, first I want to say sorry if this is not the right place to ask this question. I have a following table in sqlalchemy:

the mailing list is more appropriate but since github gives us the "question" label we can handle it

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)

This is to model the EAV model which fits my business purpose.

Now to use it easily in the code I have the following 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
    ...

This works fine when I try to get and set the fields in Python code. But I still have a problem:

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

This does not work out of the box but I had an idea of using hybrid.expression where we use a case statement:

@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),
            ...
        ]
    )

This in theory works, for example, the SQL generated for query session.query(EAVTable.value = 123 looks like:

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;

Which semantically looks like what I like, but later I find that the case expression requires all the cases have the same type, or they are cast into the same type.

that might be kind of database specific, in that I bet SQLite would let it go right through, but Postgresql would not. I'd be curious to see what the error message you are getting is.

I understand this is a requirement from the SQL language and has nothing to do with sqlachemy, but for more seasoned sqlalchemy user, is there any easy way to do what I want to achieve? Is there a way to walk around this constraint?

I think what you need to do is have the expression that's coming out on the left hand side respond to the kind of right hand expression you are comparing towards. The other way is to just cast both sides to a string. For either one, you'd need to use the @comparator hook, rather than @expression. There is an example of this, doing the same CASE thing you're doing, in the examples at https://docs.sqlalchemy.org/en/latest/_modules/examples/vertical/dictlike-polymorphic.html. note: the example works with the current SQLAlchemy 1.2 releases but not the 1.3 series which is a bug I will fix now.

[zzzeek]

related bug is #4446

[bobfang1992]

Hi thanks, I will take a look into the @comparator hook.