[Django]-Complicated Django Query

by

6👍

✅

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
)
👤giant_teapot

1👍

I believe you should be able to use something like this to get what you’re 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 shifts or excluded_shifts being executed before the query is run; they’re lazy and will only be included as subqueries in the final queryset you’re creating.

In pseudo-sql, I think the above will correspond to the following (just going off of 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 might notice, there’s a little bit of double work being performed by the DB here in those two subqueries, but I don’t think there’s a way to avoid this.

👤Dan Loewenherz

0👍

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)

Which yields SQL like:

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> )
👤Steven

Leave a comment