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?

Solution

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

Comments

[void]

hmm, this works, I dont quite get it how but it just works :D ... why do I look in User.objects.filter ? and not in UserJob.objects.filter ? if you can ... can you give me a little sql of how this will look like ... anyway thx a lot this is just what I needed ... thx for your time and GL

[Jarret Hardie]

You're filtering off the User object because what you want back is a list of Users (this based on the fact that the problem-domain question is "Which users have both job a and job c?"). It's also semantically easier to formulate a sane django query for that problem by starting with the User object. You can see the SQL statements that django generates by inspecting the results of django.db.connection: from django.db import connection; print connection.queries