[Django]-Django filtering QueryString by MultiSelectField values?

5👍

How about using Django Q sets for this, so your filter would look like:

...objects.filter( Q(topic__exact = cat) | Q(topic__startswith = '%s,' % cat) | Q(topic__endswith = ',%s' % cat) | Q(topic__contains = ',%s,' % cat),
other_attributes = 'xxx',

3👍

MultiSelectField stores vales as a comma separated string in a CharField, so you basically want .topic__contains=topic instead of .topic__in=topic

However, depending on your search form, you’ll want to join the conditions using different boolean operators to either narrow or widen the results. Depending on your needs, I would do one of the following:

  • Use Q to build a complex query using OR joins (more complex, but more versatile)
  • Unpack named parameters from a dict into a .filter() (simpler, but constraining)

Dynamically build the query with Q.

This is the better method. It lets you use OR, AND, NOT or XOR in the query to refine search options.

# ...
search_params = Q()

# Let's say we have other search parameters also
search_params = search_params | Q(note__contains="example note")

if form.cleaned_data['topic']:
    search_params = search_params | Q(topic__in=form.cleaned_data['topic'])

# ...
return qs.filter(search_params)

The resulting MYSQL query will look something like this:

SELECT * FROM `search_form`
    WHERE (
        `note` LIKE '%example note%' OR
        `topic` LIKE '%about cats%'
    )

Unpack named parameters from a dict

This method can only be used to narrow the search results using AND in the query.

# ...

search_params = {}

# let's say there are other items in the query
search_params['note__contains'] = 'example note'

if form.cleaned_data['topic']:
    search_params['topic__contains'] = form.cleaned_data['topic']

# ...
# unpack the search_params into the named parameters
return qs.filter(**search_params)

This will effectively build the Django query something like:

SearchForm.objects.filter(
    note__contains="example note",
    topic__contains="about cats"
)

The resulting database query will be something like:

SELECT * FROM `search_form`
    WHERE (
        `note` LIKE '%example note%' AND 
        `topic` LIKE '%about cats%'
    )

2👍

MultiSelectField is basically a CharField that stores the multiple choices values as a comma separated string.

Therefore you need a full table scan to perform this kind of QuerySet filtering on a MultiSelectField. You can use __regex field lookup to filter your QuerySet to match a given choice value:

(...)
searched_topics = form.cleaned_data['topic']
if searched_topics:
    search_topic_regexp = "(^|,)%s(,|$)" % "|".join(searched_topics)
    search_params.update({'topic__regex': search_topic_regexp})
(...)

For better performances (to avoid the full table scan on the topic field when you have a lot of Conversation entries), you should not use MultiSelectField but a ManyToMany relationship (that would use a separated join table).

Leave a comment