Django query for vendors who supply all parts

Question

Given the model (vendors supplying parts, many-to-many relation):

from django.db import models

class Part(models.Model):
    pass

class Vendor(models.Model):
    name = models.CharField(max_length=100)
    parts = models.ManyToManyField(Part)
    def __str__(self):
        return "%s" % self.name

Is it possible to write a Django query to get vendors who supply all of the parts? I'm interested in a query which will produce single SQL statement.

For example, with the following data:

p1 = Part()
p2 = Part()
p3 = Part()
v1 = Vendor(name="supply nothing")
v2 = Vendor(name="supply some parts")
v3 = Vendor(name="supply all parts")
p1.save()
p2.save()
p3.save()
v1.save()
v2.save()
v3.save()
v2.parts.add(p1,p2)
v3.parts.add(p1,p2,p3)

I want to get:

[<Vendor: supply all parts>]

Check this out: http://stackoverflow.com/questions/13270513/django-manytomany-filter-matching-on-all-items-in-a-list?answertab=votes#tab-top, sounds reasonable to me. — Shang Wang, Jan 07 '16 at 17:19
Well I'm not actually quite sure about the loop part in the answer I found, because I think the first part is enough. Check out this one: http://stackoverflow.com/questions/15977126/sql-server-select-rows-that-match-all-items-in-a-list. Please try it out and see what's the difference before and after the loop. — Shang Wang, Jan 07 '16 at 17:36

Gocht · Answer 1 · 2016-01-07T17:35:08.017

0

You could first query for all parts:

parts = Part.objects.all()

And then filter vendors:

vendors = Vendor.objects.filter(parts=parts)

edited Jan 07 '16 at 17:35

answered Jan 07 '16 at 17:21

Gocht

9,924
3
42
81

`Cannot resolve keyword 'part_set' into field. Choices are: id, name, parts`. I get the idea, but anyway this will produce two SQL queries, with list of parts traveling from and to database server. I'm looking for a single SQL statement solution. – Egor Rogov Jan 07 '16 at 17:34
@EgorRogov you have used a `related_name`, it is not in your question. I have edited my answer. Try it now – Gocht Jan 07 '16 at 17:35
Nope, no related_name. Btw, updated version doesn't work too (`more than one row returned by a subquery used as an expression`). – Egor Rogov Jan 07 '16 at 17:42
@EgorRogov I have the exact structure here and it is working, what is your Django's version? – Gocht Jan 07 '16 at 17:46
Models are exactly the same as described, I prepared them for this question. Database contains exactly the same data as described. There is nothing more. – Egor Rogov Jan 07 '16 at 18:58

Django query for vendors who supply all parts

1 Answers1