Django - get objects that have a related objects with certain field only

Question

Django 1.11, Python 2.7

Given the following models:

class Person(models.Model):
    objects = PersonManager()
    ...

class Job(models.Model):
    person = models.ForeignKey(
        to=Person,
        related_name='jobs'
    )
    workplace = models.ForeignKey(
        to=Workplace,
        related_name='workers'
    )
    position = models.CharField(db_index=True, max_length=255, blank=False, null=False, choices=(
        (POSITION_1, POSITION_1),
        (POSITION_2, POSITION_2),
        (POSITION_3, POSITION_3),
    ))

A person can have several jobs with the same position and with different positions too.

person1: [workplace1, POSITION_1], [workplace1, POSITION_2],
         [workplace2, POSITION_1]

person2: [workplace1, POSITION_2],
         [workplace2, POSITION_2]

person3: [workplace3, POSITION_3]

I want to write a single method in PersonManager which will retrieve all persons with multiple jobs of a certain given position (and that position only); or if multiple positions are given then persons that work in all of these positions.

• Person.objects.get_multiple_jobs(jobs=[]) will return person1, person2
• Person.objects.get_multiple_jobs(jobs=[POSITION_2]) will return person2 ONLY (as he's the only one with only POSITION_2 multiple jobs).
• Person.objects.get_multiple_jobs(jobs=[POSITION_1, POSITION_2]) will return person1
• Person.objects.get_multiple_jobs(jobs=[POSITION_3]) won't return anything

Edit 1: To clarify, I want the persons, with multiple jobs, that have jobs in ALL listed positions and ONLY them.

Using Person.objects.annotate(position_count=Count('jobs')).filter(position_count__gt=1, jobs__position__in=[...]) won't work as in the 3rd case I'll get person2 as well.

Chaining filter/exclude like Person.objects.filter(jobs__position=POSITION_1).exclude(jobs__position__in=[POSITION_1,POSITION_3] will work but it's not maintainable - what if in the future more positions will be added? Deciding which jobs to exclude dynamically is cumbersome. It also results in filters being very hard-codded when I wanted to encapsulate the logic under a single method of PersonManager.

I was thinking about using Q in the filtering or a sub-query but I can't come with a working query which is modular enough to work on all cases and enable maintainability too.

Edit 2:

The solution, per Dušan Maďar's answer, after adjusting it to django 1.11:

I tried to change the syntax first and replace the inside filter of the Count with a simple Case When:

 annotations['cnt_{}'.format(pos)] = Count(
                    Case(
                        When(
                            jobs__position=pos,
                            then=1
                        ),
                        default=0,
                        output_field=IntegerField()
                    )
                )

But that didn't work.

The resulting query was:

SELECT "person"."id",
       "person"."name",
        ...
        COUNT(CASE WHEN "job"."position" = \'Driver\' TEHN 1 ELSE 0 END) AS "cnt_Driver"
FROM "person" LEFT OUTER JOIN **long and irrelevant**

After playing with the SQL itself I found the sub-query I needed to make it work, as COUNT wouldn't do:

(SELECT COUNT(*) from "job" WHERE "job"."position" = 'Driver' and "job"."person_id" = "person"."id" ) as "cnt_Driver"

To reach this sub-query via django:

sub =  Job.objects.filter(
             person=OuterRef('pk'),
             position=pos
       ).values('person').annotate(c=Count('*')).values_list('c')

The .values('person') is important - django adds its own GROUP BY clause which includes all the values of the schema (and thus only one result per person since all the Jobs are distinctly grouped), and with this step the GROUP BY will consist only from "person"."id".

Answer 1

Here's how I was able to make it work according to your rules. Please note it's a class method on Person model, not a manager method.

class Person(models.Model):
   ...

    @classmethod
    def get_multiple_jobs(cls, positions=None):
        if positions is None:
            positions = []

        annotations = {}
        filters = []
        if positions:
            operator_ = "gt" if len(positions) == 1 else "gte"
            for position in positions:
                annotations[f"cnt_{position}"] = Count(
                    "jobs__position", filter=Q(jobs__position=position)
                )
                filters.append(Q(**{f"cnt_{position}__{operator_}": 1}))
            annotations["cnt_positions"] = Count("jobs__position", distinct=True)
            filters.append(Q(cnt_positions=len(positions)))
        else:
            annotations["cnt_positions"] = Count("jobs__position")
            filters.append(Q(cnt_positions__gt=1))

        return Person.objects.annotate(**annotations).filter(*filters).distinct()

You can call it as Person.get_multiple_jobs().