CODEWITHSUNDEEP
pythondjango
Etienne
Etienne

Reputation: 12580

Complex Django query

I have theses models:

class Person(models.Model):
    position = models.CharField(max_length=64)
    group = models.ForeignKey('Group')

class Group(models.Model):
    ...

Now I want a query that will give me back all the distinct Group where all the persons in each group have the position worker. So I will have all the groups with only workers in them. How to do that?

I started with:

groups = Group.objects.filter(person__position='worker').distinct()

But with this I have groups that have persons that are not worker.

For example my query should return group1 but not group2:

group1
    person1 -> position=worker
    person2 -> position=worker
    person3 -> position=worker

group2
    person4 -> position=worker
    person5 -> position=looser
    person6 -> position=sleeper

Upvotes: 1

Views: 256

Answers (2)

XORcist
XORcist

Reputation: 4367

This should work:

Group.objects.exclude(~Q(person__position="worker"))

Edit:

The above wasn't correct so here's my second try:

Group.objects.filter(
    Q(person__position="worker") 
    & ~Q(person__position__gt="worker")
    & ~Q(person__position__lt="worker")
)

I am unsure if this can be used with all databases. It works with my PGSQL installation.

Upvotes: 2

baddox
baddox

Reputation: 81

Look into annotation: http://docs.djangoproject.com/en/dev/topics/db/aggregation/

I can't recall the exact syntax, but you should be able to annotate each Group with the number of Persons with position="worker" as well as the total number of Persons, then filter it to only the Groups where those two counts are equal.

Upvotes: 0

Related Questions