1

I have a datamodel where I store a list of values separated by comma (1,2,3,4,5...).

In my code, in order to work with arrays instead of string, I have defined the model like this one:

class MyModel(db.Model):
    pk = db.Column(db.Integer, primary_key=True)
    __fake_array = db.Column(db.String(500), name="fake_array")

    @property
    def fake_array(self):
        if not self.__fake_array:
            return

        return self.__fake_array.split(',')

    @fake_array.setter
    def fake_array(self, value):
        if value:
           self.__fake_array = ",".join(value)
        else:
           self.__fake_array = None

This works perfect and from the point of view of my source code "fake_array" is an array, It's only transformed into string when it's stored in database.

The problem appears when I try to filter by that field. Expressions like this doesn't work:

MyModel.query.filter_by(fake_array="1").all()

It seems that I cant filter using the SqlAlchemy query model.

What can I do here? Is there any way to filter this kind of fields? Is there is a better pattern for the "fake_array" problem?

Thanks!

  • Imagining this was pure SQL, how would your query look? Or do you accept solutions where the query doesn't filter, but you filter in Python afterwards? – Halvor Holsten Strand Aug 14 at 11:04
  • I'd expect this: MyModel.query.filter_by(fake_array="1").all() --> Select * From MyModel where fake_array = '1' – Curro Aug 14 at 12:02
  • And event this: MyModel.query.filter(MyModel.fake_array.like('%1%')).all() --> Select * From MyModel where fake_array like '%1%' – Curro Aug 14 at 12:04
0

What you're trying to do should really be replaced with a pair of tables and a relationship between them.

The first table (which I'll call A) contains everything BUT the array column, and it should have a primary key of some sort. You should have another table (which I'll call B) that contains a primary key, a foreign key column to A (which I'll call a_id, and an integer field.

Using this layout, each row in the A table has its associated array in table B where B's a_id == A.id via a join. You can add or remove values from the array by manipulating the rows in table B. You can filter by using a join.

If the order of the values is needed, then create an order column in table B.

  • Sure! that'd be the best solution in a strict relational model. But sometimes I prefer this kind of tricks in my code rather than a ton of tables. Please, don't kill me for that ;). – Curro Aug 13 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.