Django filtering QueryString by MultiSelectField values?

Question

I'm using MultiSelectField to store a topic/topic's of conversation

My model looks somewhat like this:

class Conversation(models.Model):
    (...)
    note= models.CharField(max_lenght=250)
    TOPIC_CHOICES =(
        (1,"about cats"),
        (2, "about dogs"),
        (3, "about fluffy little things"),
    )
    topic =MultiSelectField(choices=TOPIC_CHOICES)

I'm using ListView and filtering by GET parameters inside get_queryset:

form extract:

class SearchForm(Form):
    (...)
    topic = MultipleChoiceField(choices=Conversation.TOPIC_CHOICES, required=False)

get_queryset extract :

(...)
if form.cleaned_data['topic']:
                search_params.update({'topic__in': form.cleaned_data['topic']})
(...)
return qs.filter(**search_params)

This method worked fine for single value choice fields.

But in this case if I for ex. select in form "about cats" I got only objects that topic is set to cats only("about cats" and nothing else -single value).

What I want is all objects in which one of topic values is 1-"about cats". This mean if some object have topic=1,3(cats and fluffy things) it should appear too

Second scenario: I select "about cats" and "about dogs" in form - I want all objects that have cats as one of the topic's and all objects that have a dogs as one of the topic's Right now when I select more than one option for ex. cats and dogs I get all that have only cats and all that got only dogs as a topic

Is there any other field lookup string instead of __in that will achieve that? If not what is most hassle free way to do that?

How does the form.cleaned_data['topic'] return? Is it a list of strings or just a string? — tomwalker
– tomwalker, Commented Jul 28, 2014 at 14:53
logger.debug(form.cleaned_data[topic]) prints out : DEBUG [u'1', u'2'] so it's a list PS Look at my latest edit - I made a false statement in the beginning and corrected it now. — Lord_JABA
– Lord_JABA, Commented Jul 28, 2014 at 15:41
Try: if form.cleaned_data['topic']: search_params.update({'topic__in': map(int, form.cleaned_data['topic'])}) — tomwalker
– tomwalker, Commented Jul 28, 2014 at 15:53

Jens Lundstrom · Accepted Answer · 2016-01-22 18:08:12Z

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',

answered Jan 22, 2016 at 18:08

Jens Lundstrom

7228 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adonis Gaitatzis · Accepted Answer · 2020-12-10 16:58:22Z

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%'
    )

pchiquet · Accepted Answer · 2014-07-29 11:40:05Z

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).

edited Jul 29, 2014 at 11:40

answered Jul 28, 2014 at 16:10

pchiquet

3,1971 gold badge15 silver badges15 bronze badges

7 Comments

Lord_JABA Over a year ago

Does m2m approach will have any impact in case i only will be using it to 1-filter 2- sort ? This will always check topics of all enteries in question.

pchiquet Over a year ago

Yes, because your database should use an index to filter the Conversation entries based on their related topics. The performance of this SQL request (and the difference with the MultiSelectField approach) depends on the total number of Conversation entries and on the number of entries returned.

Lord_JABA Over a year ago

So if i add a model "topic" with one field named "name" it will work faster even if then it need to search two tables instead of one? Sorry for keeping asking stupid questions but im trying to understand...

pchiquet Over a year ago

It will be faster "most of the time" (when you have enough Conversation entries and when the number of Conversations that match the requested topic is not too big)

Lord_JABA Over a year ago

I will rather stick to MultiChoice field because no. of topics in system is rather small- 3 as for this time- and no. of conversations will be rather huge so number of conversations per topic will be big. Is there a way to adapt this answer to work with multiple topics? I mean for each topic in form.cleaned_data['topic'] append OR topic to search_choice_regxp - I cant figure out how to format the expression to make it possible to append next topic to the end of string.

|

Collectives™ on Stack Overflow

Django filtering QueryString by MultiSelectField values?

3 Answers 3

Comments

Dynamically build the query with Q.

Unpack named parameters from a dict

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Dynamically build the query with Q.

Unpack named parameters from a dict

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related