Clever Geek Handbook

Complex Django query

My hodgepodge app queries don't match my knowledge of how Django ORM works.

Here's my current (wrong) attempt:

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

See my models below if you'd like to read them.

The original filter()

works fine. My problem is exclude()

chained to the end.

exclude()

seems to exclude Mentor

with:

  • related Shift

    that matches the specified conditions ( shift__session = session

    ),
  • And (possibly another) related Shift

    one that meets the second set of criteria shift__jobs__time = job.time

    .

I only want to filter out Mentor

which have an associated Shift

one that meets the BOTH 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 name."
    )

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

    location = models.CharField(
        max_length = 50,
        help_text = "The job 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
    )
+3


source to share


3 answers


First, explain what's going on here. When you write:

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

This means 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, you meant in your request:

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

Keep this in mind when you write a filter exclude

that has multiple parameters.

So, if in your query you want to exclude items that check the BOTH criterion (criterion intersection, if you like), the easiest and best way to do it is filters exclude chaining

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

Queryset operations are lazy, which means your filters exclude

will evaluate at the same time. Thus, the two filters will not "work twice".

The filter is converted to:

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

This is exactly what you want!

Written requests can sometimes be difficult, but remember:



  • exclude with multiple args translation into: not (A) OR no (B) OR no (C) ...
  • if you need to exclude items from a set of factors (AND), just make a few calls to the filter exclude

    .

Now this is your new request:

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 ask, you want:

  • session related records: filter(shift__session = session)

  • but also ... that does not apply to this session.exclude(shift__session = session)

The generated SQL will be:

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

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

From your post I read:

excluding [...] the associated Shift that matches the specified conditions (shift__session = session) AND (possibly another) associated Shift that matches the second set of criteria

Used filter

already guarantees that shift__session = session

, so you shouldn't put it in a filter exclude

.

From what I think (but tell me if I'm wrong) you want:

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


source


I believe you should use something like this to get what you are looking for.

shifts = Shift.objects.filter(session=session)
excluded_shifts = shifts.filter(jobs__time=job.time)
queryset = Mentor.objects.filter(
    jobs_desired=job
    shift__in=shifts
).exclude(
    shift__in=excluded_shifts
)

Don't worry about executing shifts

or excluded_shifts

before running a query; they are lazy and will only be included as subqueries in the final query you create.



In pseudo sql, I think the above would match the following (just moving away from past experience here):

SELECT *
FROM mentor
LEFT JOIN jobs_desired ON (mentor.id=jobs_desired.mentor_id)
WHERE jobs_desired.id=1
AND shift_id IN (
    SELECT id
    FROM shifts
    WHERE session_id=2
)
AND shift_id NOT IN (
    SELECT id
    FROM shifts
    LEFT JOIN jobs ON (shifts.id=jobs.session_id)
    WHERE session_id=2
    AND jobs.time='B'
)

As you may have noticed, there is a bit of double work done by the DB here in these two subqueries, but I don't think there is a way to avoid this.

+1


source


How about using Q functions ?

from django.db.models import Q
queryset = Mentor.objects.exclude(
        Q(shift__jobs__time=job.time) & Q(shift__session=session)
    ).filter(jobs_desired=job, shift__session=session)
print str(queryset.query)

What SQL gives as:

SELECT "your_project_mentor"."id", "your_project_mentor"."uni", "your_project_mentor"."uni_study", "your_project_mentor"."work", "your_project_mentor"."shirt_size", "your_project_mentor"."needs_shirt", "your_project_mentor"."wwcc", "your_project_mentor"."wwcc_receipt", "your_project_mentor"."curtin_status", "your_project_mentor"."curtin_id", "your_project_mentor"."coding_experience", "your_project_mentor"."children_experience", "your_project_mentor"."user_id" 
  FROM "your_project_mentor" 
  INNER JOIN "your_project_mentor_jobs_desired" 
    ON ( "your_project_mentor"."id" = "your_project_mentor_jobs_desired"."mentor_id" ) 
  INNER JOIN "your_project_shift" 
    ON ( "your_project_mentor"."id" = "your_project_shift"."mentor_id" ) 
  WHERE 
    (NOT 
      ("your_project_mentor"."id" IN 
        (SELECT U1."mentor_id" 
          FROM "your_project_shift" U1 
          INNER JOIN "your_project_shift_jobs" U2 
            ON ( U1."id" = U2."shift_id" ) 
          INNER JOIN "your_project_job" U3 
            ON ( U2."job_id" = U3."id" ) 
          WHERE U3."time" = <job_time> ) 
      AND "your_project_mentor"."id" IN 
        (SELECT U1."mentor_id" 
          FROM "your_project_shift" U1 
          WHERE U1."session_id" = <session_id> )
      ) 
    AND "your_project_mentor_jobs_desired"."job_id" = <job_id>  
    AND "your_project_shift"."session_id" = <session_id> )
0


source






More articles:


All Articles