CODEWITHSUNDEEP
pythondjangodjango-queryset
Lord_JABA
Lord_JABA

Reputation: 2625

Django filtering QueryString by MultiSelectField values?

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?

Upvotes: 3

Views: 4978

Answers (3)

Adonis Gaitatzis
Adonis Gaitatzis

Reputation: 3729

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

Upvotes: 3

Jens Lundstrom
Jens Lundstrom

Reputation: 712

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

Upvotes: 5

pchiquet
pchiquet

Reputation: 3177

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

Upvotes: 2

Related Questions