Django search multiple filters

Question

Lets say I have a model

models.py

class user:
    name = models.CharField(max_length=25)

class job:
    job_name = models.CharField(max_length=25)

class user_job:
    user = models.ForeignKey('user')
    job  = models.ForeignKey('job')

forms.py

 jobs = (
     ('0', 'a'),
     ('1', 'b'),
     ('2', 'c'),
 )

 class searchForm:
     box = forms.ModelMultipleChoiceField(
                                          choices = jobs,
                                          widget  = forms.CheckboxSelectMultiple(),
                                          label   = 'Search',
                                         )

I can search for users that have job 'a' with

user_job.objects.filter(job__exact = 'a') ...

I tried to search for users that have both job 'a' and job 'c' like so

search_q = user_job.objects.filter(job__exact = 'a')
search_q = search_q.filter(job__exact = 'c')

but I get all users that have job 'a' or job 'c', and I need all users that have both jobs.

Is there a way I can filter it through Django, or do I need to filter on one job and then iterate through the results and check for the 2nd job?

Answer 1

You'll probably find it easier to search from the User model since what you want is a list of Userss who have both jobs. Django automatically sets up properties on your models that allow you to access related models both from model instances and in DB queries.

Assuming you set up your models like so:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=25)

    def __repr__(self):
        return '<User: %s>' % self.name

class Job(models.Model):
    name = models.CharField(max_length=25)

    def __repr__(self):
        return '<Job: %s>' % self.name

class UserJob(models.Model):
    user = models.ForeignKey(User)
    job = models.ForeignKey(Job)

    def __repr__(self):
        return '<UserJob: %s %s>' % (self.user.name, self.job.name)

And populate it as follows:

u1 = User.objects.create(name='u1')
u2 = User.objects.create(name='u2')
u3 = User.objects.create(name='u3')

a = Job.objects.create(name='a')
b = Job.objects.create(name='b')
c = Job.objects.create(name='c')

UserJob.objects.create(user=u1, job=a)
UserJob.objects.create(user=u2, job=a)
UserJob.objects.create(user=u2, job=b)
UserJob.objects.create(user=u3, job=a)
UserJob.objects.create(user=u3, job=c)

The following query will return you user 3, which is the only user who has both "Job a" and "Job c":

u = User.objects.filter(userjob__job=a).filter(userjob__job=c)

(or, if you prefer to refer to the jobs by name rather than Job instance):

u = User.objects.filter(userjob__job__name='a').filter(userjob__job__name='c')

You can see how Django is allowing you to traverse the related fields from the User model to the UserJob model with the double underscore notation (the Django docs on this are here: http://docs.djangoproject.com/en/1.2/topics/db/queries/#lookups-that-span-relationships.

Once you get the User object back, you can similarly access the UserJob instances using the relationship properties that Django adds to the model:

u = User.objects.filter(userjob__job__name='a').filter(userjob__job__name='c')
jobs = u.userjob_set.all()