python - 复杂的 Django 查询

Question

我的大杂烩应用程序的查询超出了我对 Django 的 ORM 工作原理的了解。

这是我当前的(不正确的)尝试:

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

如果你想通读它们，我的模型在下面。

最初的 filter() 工作正常。我的问题是链接到末尾的 exclude()。

exclude() 似乎在排除 Mentor:

• 满足指定条件的关联 Shift(shift__session = session),
• 和一个(可能不同的)关联的 Shift 满足第二组标准 shift__jobs__time = job.time。

我只想过滤掉 Mentor 的 Shift 与之关联，同时满足两个条件。

有什么想法吗？

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

首先，让我们解释一下这里发生了什么。当你写的时候:

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

这转化为以下查询:

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

在boolean algebra , ¬(A ∧ B) (not[A and B]) 实际上是 (¬A ∨ ¬B) (not[A] OR not[B])。因此，您在查询中的意思是:

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

在编写具有多个参数的exclude过滤器时，请牢记这一点。

因此，如果在您的查询中，您想要排除同时检查两个标准的元素(如果您愿意，则为标准的交集)，最简单和最好的方法是链排除过滤器 :

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

查询集操作是惰性，大致意味着您的exclude 过滤器将同时被评估。所以两个过滤器不会“加倍工作”。

过滤器将转化为:

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

这正是您想要的!

编写查询有时会很困难，但请记住:

• 排除多个参数转换为:not(A) OR not(B) OR not(C)...
• 如果您需要根据因素组合 (AND) 排除项目，只需多次调用 exclude 过滤器即可。

---

现在，这是您的新查询:

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

如果我们“扁平化”你的要求，你想要:

• 属于 session 的记录:filter(shift__session = session)
• 而且...不属于该 session .exclude(shift__session = session)

生成的 SQL 将是:

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

但是 A ∧ ¬A (A AND NOT[A]) 是空集。所以问题出在您查询的语义上。

从你的帖子我读到:

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

您使用的 filter 已经保证 shift__session = session，所以您不应该将它放在 exclude 过滤器中。

根据我的猜测(如果我错了请告诉我)，你想要的是:

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