filtering by one-digit number in MultiSelectField

Question

I have MyModel:

class MyModel(models.Model):
    ELEMENTS_CHOICES = Choices(
        (1, 'element1'),
        (2, 'element2'),
        (3, 'element3'),
        (4, 'element4'),
        (10, 'element10'),
        (12, 'element12'),
    )
    elements = MultiSelectField(
        _('elements'),
        choices=ELEMENTS_CHOICES,
        blank=True,
    )

I have also some MyModel objects with elements values like below:

obj1.elements = ['1', '2', '3', '10', '12']
obj2.elements = ['1', '3', '10', '12']
obj3.elements = ['1', '3', '10']
obj4.elements = ['2']

How do I filter through this field values?

The filter I tried to use is:

search_id = 2
MyModel.objects.filter(elements_icontains=search_id)

It was ok before the two-digit numbers appeared in the MyModel field choices. Now it returns obj1, obj2 (because 12 is on the list, and it contains 2 as the part of it) and obj4.

I would like to search just for values with 2 number and get just obj1 and obj4 as results. Currently, the results are objects that contains 2 and 12.

What is the right way to filter this field?

What is the database type you are using and why did chose to store it as delimited list in database, there are many drawbacks certainly regarding filtering performance https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — iklinac, Apr 16 '21 at 10:12
@iklinac postgres 9.4. The database structure was not my choice, I just try to fix this filter with no database changes. — Dibidalidomba, Apr 16 '21 at 10:18

Abdul Aziz Barkat · Answer 1 · 2021-04-16T11:01:59.530

Storing data as comma-separated lists in the database is not a great technique and it is generally advised against doing so. Your use case itself implies that you are facing this problem. If it is feasible you should redesign your model schema (Recommended) and use a ForeignKey or a ManyToManyField to achieve your goal.

If it is unfortunately not possible to change this schema for some reason you can only resolve to using regex to query your database:

import re


search_id = 2
filter_term = r'\b%s\b' % re.escape(str(search_id)) # \b is word boundary in regex
MyModel.objects.filter(elements__iregex=filter_term)

iklinac · Answer 2 · 2021-04-17T18:09:01.517

You should propose proper database normalization for this particular problem instead.

If you still find yourself as having to go without changing database you could do it two way filter regex or even better by using string_to_array() as it can be indexed

This will require you to perform raw() query something in a line of

WHERE 2 = any(string_to_array(elements));

You can then add Functional Index for added performance benefit

filtering by one-digit number in MultiSelectField

2 Answers2