Complicated Django Query

Question

My hodgepodge application's queries are going beyond my knowledge of how Django's ORM works.

Here's my current (incorrect) attempt:

queryset = Mentor.objects.filter(
    shift__session = session,
    jobs_desired = job
).exclude(
    shift__session = session,
    shift__jobs__time = job.time
)

My models are below if you want to read through them.

The initial filter() works fine. My issue is the exclude() chained onto the end.

The exclude() seems to be excluding Mentors with:

• an associated Shift which meets the conditions specified (shift__session = session),
• AND a (possibly different) associated Shift that meets the second set of criteria shift__jobs__time = job.time.

I only want to filter out Mentors that have a Shift associated with them that meets BOTH the criterion.

Any ideas?

class DojoSession(models.Model):
    term = models.ForeignKey(DojoTerm, help_text = "Dojo Term")
    date = models.DateField(
        blank = False,
        help_text = "Date during which the session will take place."
    )

    start = models.TimeField(
        blank = False,
        help_text = "Start Time"
    )

    end = models.TimeField(
        blank = False,
        help_text = "End Time"
    )

    rooms = models.ManyToManyField(
        Room,
        blank = True,
        help_text = "The rooms in which this session will be running."
    )

class Shift(models.Model):
    mentor = models.ForeignKey(
        'mentors.Mentor',
        blank = False,
        help_text = 'The mentor unergoing this shift.'
    )

    session = models.ForeignKey(
        DojoSession,
        blank = False,
        help_text = 'The session during which this shift takes place.',
    )

    role = models.ForeignKey(
        'mentors.Role',
        blank = False,
        help_text = "The role that the mentor will be undertaking during this shift.",
    )

    room = models.ForeignKey(
        Room,
        blank = True,
        null = True,
        help_text = "The room, if any, that the mentor will be undertaking the shift in."
    )

    jobs = models.ManyToManyField(
        'jobs.Job',
        blank = True,
        null = True,
    )

    start = models.TimeField(
        blank = False,
        help_text = "Start Time"
    )

    end = models.TimeField(
        blank = False,
        help_text = "End Time"
    )

class Job(models.Model):
    BEFORE = 'B'
    DURING = 'D'
    AFTER = 'A'

    TIME_CHOICES = (
        (BEFORE, 'Before session'),
        (DURING, 'During session'),
        (AFTER, 'After session'),
    )

    name = models.CharField(
        max_length = 50,
        help_text = "The job's name."
    )

    description = models.TextField(
        max_length = 1024,
        help_text = "A description of the job."
    )

    location = models.CharField(
        max_length = 50,
        help_text = "The job's location."
    )

    time = models.CharField(
        max_length = 1,
        choices = TIME_CHOICES,
        help_text = "The time during a session at which this job can be carried out."
    )

class Mentor(models.Model):
    MALE_SMALL = "MS"
    MALE_MEDIUM = "MM"
    MALE_LARGE = "ML"
    MALE_EXTRA_LARGE = "MXL"

    FEMALE_EXTRA_SMALL = "FXS"
    FEMALE_SMALL = "FS"
    FEMALE_MEDIUM = "FM"
    FEMALE_LARGE = "FL"
    FEMALE_EXTRA_LARGE = "FXL"

    SHIRT_SIZE_CHOICES = (
        ('Male', (
            (MALE_SMALL, "Male S"),
            (MALE_MEDIUM, "Male M"),
            (MALE_LARGE, "Male L"),
            (MALE_EXTRA_LARGE, "Male XL")
        )),
        ('Female', (
            (FEMALE_EXTRA_SMALL, "Female XS"),
            (FEMALE_SMALL, "Female S"),
            (FEMALE_MEDIUM, "Female M"),
            (FEMALE_LARGE, "Female L"),
            (FEMALE_EXTRA_LARGE, "Female XL")
        ))
    )

    ASSOCIATE = 'A'
    STAFF = 'S'
    NEITHER = 'N'

    CURTIN_STATUS_CHOICES = (
        (ASSOCIATE, 'Associate'),
        (STAFF, 'Staff'),
        (NEITHER, 'Neither/not sure')
    )

    NOTHING = 'NO'
    SOMETHING = 'SO'
    EVERYTHING = 'EV'

    KNOWLEDGE_CHOICES = (
        (NOTHING, 'I know nothing but am keen to learn!'),
        (SOMETHING, 'I know some basics'),
        (EVERYTHING, 'I know a great deal')
    )

    uni = models.CharField(
        max_length = 50,
        null = True,
        blank = True,
        help_text = "University of study"
    )

    uni_study = models.CharField(
        max_length = 256,
        null = True,
        blank = True,
        help_text = "If you're attending university, what are you studying?"
    )

    work = models.CharField(
        max_length = 256,
        null = True,
        blank = True,
        help_text = "If you workwhat do you do?"
    )

    shirt_size = models.CharField(
        max_length = 3,
        blank = True,
        choices = SHIRT_SIZE_CHOICES,
        help_text = "T-shirt size (for uniform)"
    )

    needs_shirt = models.BooleanField(
        default = True,
        help_text = "Does the mentor need to have a shirt provisioned for them?"
    )

    wwcc = models.CharField(
        max_length = 10,
        verbose_name = "WWCC card number",
        blank = True,
        null = True,
        help_text = "WWCC card number (if WWCC card holder)"
    )

    wwcc_receipt = models.CharField(
        max_length = 15,
        verbose_name = "WWCC receipt number",
        blank = True,
        null = True,
        help_text = "WWCC receipt number (if WWCC is processing)"
    )

    curtin_status = models.CharField(
        max_length = 1,
        verbose_name = "Current Curtin HR status",
        choices = CURTIN_STATUS_CHOICES,
        default = NEITHER,
        blank = False,
        help_text = "When possible, we recommend that all CoderDojo mentors are either Curtin University Associates or Staff members."
    )

    curtin_id = models.CharField(
        max_length = 10,
        verbose_name = "Curtin Staff/Associate ID",
        blank = True,
        null = True,
        help_text = "Your Curtin Staff/Associate ID (if applicable)"
    )

    coding_experience = models.CharField(
        max_length = 2,
        blank = False,
        default = NOTHING,
        choices = KNOWLEDGE_CHOICES,
        help_text = "How much programming experience do you have?"
    )

    children_experience = models.CharField(
        max_length = 2,
        blank = False,
        default = NOTHING,
        choices = KNOWLEDGE_CHOICES,
        help_text = "How much experience do you have with children?"
    )

    roles_desired = models.ManyToManyField(Role)

    jobs_desired = models.ManyToManyField('jobs.Job')

    shift_availabilities = models.ManyToManyField(
        'planner.DojoSession',
        help_text = "When are you available?"
    )

    user = models.OneToOneField(settings.AUTH_USER_MODEL,
        unique = True
    )

Answer 1

First, let's explain what happens here. When you write :

set.exclude( A=arg1, B=arg2 )

This translates into the following query :

SELECT [...] WHERE NOT (A=arg1 AND B=arg2)

In boolean algebra, ¬(A ∧ B) (not[A and B]) is actually (¬A ∨ ¬B) (not[A] OR not[B]). Thus what you meant in your query was :

SELECT [...] WHERE NOT(A=arg1) OR NOT(B=arg2)

Keep this in mind when you write an excludefilter that has several parameters.

So, if in your query, you want to exclude the elements that check BOTH criterion (the intersection of criterion if you will), the simplest and best way to do it is to chain exclude filters :

set.exclude(A=arg1).exclude(B=arg2)

Queryset operations are lazy, meaning roughly that your exclude filters will be evaluated at the same time. So two filters won't be "twice the work".

The filter will translate into :

SELECT [...] WHERE NOT(A=arg1) AND NOT(B=arg2)

Which is exactly what you want !

Writing queries can be sometimes hard, but remember :

• exclude with multiple args translate into : not(A) OR not(B) OR not(C)...
• if you need to exclude items on a conjunction of factors (AND), just make several calls to the exclude filter.

---

Now, this is your new query :

queryset = Mentor.objects.filter(
    shift__session = session,
    jobs_desired = job
).exclude(
    shift__session = session
).exclude(
    shift__jobs__time = job.time
)

If we "flatten" what you are asking, you want :

• records that belong to a session : filter(shift__session = session)
• but also... that don't belong to that session .exclude(shift__session = session)

The generated SQL would be :

SELECT [...] WHERE shift__session = session AND [...] AND NOT(shift__session = session)

But A ∧ ¬A (A AND NOT[A]) is the empty set. So the problem is with the semantics of your query.

From your post I read:

excluding [...] an associated Shift which meets the conditions specified (shift__session = session) AND a (possibly different) associated Shift that meets the second set of criteria

The filter you used already guarantees that shift__session = session, so you should not put it inside the exclude filter.

From what I guess (but tell me if I'm wrong), what you want is :

queryset = Mentor.objects.filter(
    shift__session = session,
    jobs_desired = job
).exclude(
    shift__jobs__time = job.time
)