Filtering an ManyToManyField causes the `.values` call to be filtered

[Ryan "RB" Barnes]

Given two following tables:

class Technician(models.Model):
    ...

class Appointment(models.Model):
    technicians = models.ManyToManyField(Technician)

If a have an appointment with two technicians assigned to it and create the follow queries I get results I would not expect

tech_1 = Technician()
tech_1.save()
tech_2 = Technician()
tech_2.save()

appointment = Appointment(technicians=[tech_1, tech_2])
appointment.save()

# No issues here
r1 = Appointment.objects.filter(id=appointment.id).values_list('technicians', flat=True)
assert tech_1.id in r1
assert tech_2.id in r1

# Now filter for a single technician
r2 = Appointment.objects.filter(id=appointment.id, technicians__in=[tech_1]).values_list('technicians', flat=True)
assert tech_1.id in r2

# Fails here as tech_2 has been filtered out
assert tech_2.id in r2

This is certainly because the SQL produced joins against the through model, but then filters it directly while also selecting from it.

I know I can get around this but using subqueries and ArrayAggs in various ways but it feels like a footgun that most users would miss.

[Simon Charette]

There's no bug here please refer to TicketClosingReasons/UseSupportChannels

Your second query literally generate

SELECT technician_id
FROM appointment
INNER JOIN appointment_technicians ON (
    appointment_technicians.appointment.id = appointment.id
)
WHERE
    appointment.id = ?
    AND appointment_technicians.technician_id IN ?

If you're interested in all technicians for a particular appointment where a particular technician is you effectively need to use Exists

Appointment.objects.filter(
    Exists(
        Technician.objects.filter(
            id=tech_1,
            appointments__in=OuterRef("pk"),
        )
    ),
    id=appointment.id,
)

[Ryan "RB" Barnes]

Hi Simon,

Thanks for the reply.

As stated at the end of my ticket I am aware of the possible workarounds but the requirement for a workaround is my problem with the SQL being generated.

My issue with the following querysets is that they produce different results because of the generated SQL and that is unexpected.

Appointment.objects.filter(id=appointment.id).values_list('technicians', flat=True)
# [1, 2]
Appointment.objects.filter(id=appointment.id, technicians__in=[tech_1]).values_list('technicians', flat=True)
# [1]

Accessing technicians from a filtered queryset produces different results

qs = Appointment.objects.filter(id=appointment.id, technicians__in=[tech_1])
qs.values_list('technicians', flat=True)
# [1]
qs.get().technicians.all()
# [1, 2]

The engineers I've talked to were not aware of this interaction as it feels unintuitive.

Either way I will keep this in mind as another thing that the ORM abstraction does not address.

Thank you for your time on this matter.