I'm rather stumped about the best way to build a Django query that checks if all the elements of a ManyToMany
field (or a list) are present in another ManyToMany
field.
As an example, I have several Person
s, who can have more than one Specialty. There are also Job
s that people can start, but they require one or more Specialty
s to be eligible to be started.
class Person(models.Model):
name = models.CharField()
specialties = models.ManyToManyField('Specialty')
class Specialty(models.Model):
name = models.CharField()
class Job(models.Model):
required_specialties = models.ManyToManyField('Specialty')
A person can start a job only if they have all the specialties that the job requires. So, again for the sake of example, we have three specialties:
And I have a Job
that requires the Singing and Dancing specialties. A person with Singing and Dancing specialties can start it, but another with Coding and Singing specialties cannot -- as the Job requires a Person who can both sing and dance.
So, now I need a way to find all jobs that a person can take on. This was my way to tackle it, but I'm sure there's a more elegant approach:
def jobs_that_person_can_start(person):
# we start with all jobs
jobs = Job.objects.all()
# find all specialties that this person does not have
specialties_not_in_person = Specialty.objects.exclude(name__in=[s.name for s in person.specialties])
# and exclude jobs that require them
for s in specialties_not_in_person:
jobs = jobs.exclude(specialty=s)
# the ones left should fill the criteria
return jobs.distinct()
This is because using Job.objects.filter(specialty__in=person.specialties.all())
will return jobs that match any of the person's specialties, not all of them. Using this query, the job that requires Singing and Dancing would appear for the singing coder, which is not the desired output.
I'm hoping this example is not too convoluted. The reason I'm concerned about this is that the Specialties in the system will probably be a lot more, and looping over them doesn't seem like the best way to achieve this. I'm wondering if anyone could lend a scratch to this itch!
When querying many-to-many relationships, avoid using multiple filter() methods, make use of Q() objects instead. You can check the SQL query of a QuerySet with str(queryset. query) . Check the performance of recently executed SQL queries with django.
A ManyToManyField in Django is a field that allows multiple objects to be stored. This is useful and applicable for things such as shopping carts, where a user can buy multiple products. To add an item to a ManyToManyField, we can use the add() function.
To create many-to-many relationships, you need to create a new table to connect the other two. This new table is called an intermediate table (or sometimes a linking or junction table).
Ok I guess I should have added this to the other answer, but when I started on it, it seemed like it was going to be a different direction haha
No need to iterate:
person_specialties = person.specialties.values_list('pk', flat=True)
non_specialties = Specialties.objects.exclude(pk__in=person_specialties)
jobs = Job.objects.exclude(required_specialties__in=non_specialties)
note: I don't know exactly how fast this is. You may be better off with my other suggestions.
Also: This code is untested
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With