CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
Dibidalidomba
Dibidalidomba

Reputation: 777

filtering by one-digit number in MultiSelectField

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?

Upvotes: 2

Views: 134

Answers (2)

iklinac
iklinac

Reputation: 15738

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

Upvotes: 1

Abdul Aziz Barkat
Abdul Aziz Barkat

Reputation: 21807

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)

Upvotes: 1

Related Questions