#Filtering on all ids in list

1 messages · Page 1 of 1 (latest)

zenith hatch
# 
class Competence(models.Model):
  name = models.CharField()

class Worker(models.Model):
  competences = models.ManyToManyField(
    to=Competence,
    related_name="workers"
  )

  objects = WorkerQueryset.as_manager()

class WorkerQueryset(query.QuerySet):
  def workers_with_required_competences(self, required_competence_ids: List[int]):
    # Return the workers who has ALL the competences in the given required_competence_ids

Can anyone help me on how to do the filtering?

self.filter(competences__ids__in=required_competence_ids)

I think the following will return if only one of them is true?

high dove
#

I think you are correct. You can build a more strict query by chaining several conditions using AND like so:

    def workers_with_required_competences(self, required_competence_ids: List[int]):
        query_has_all_required_competences = Q()
        for competence_id in required_competence_ids:
            query_has_all_required_competences &= Q(competences=competence_id)
        return self.filter(query_has_all_required_competences)

maybe there is a more elegant way to express this via the ORM that I'm not aware of right now

#

Q() objects can be chained with OR via |= or AND &= to be more relaxed or more strict in what you are querying for

zenith hatch
#

Thanks! That was a clever way to solve it.

I do however face some problems when testing this:

CapacityContribution.objects.filter(Q(service_worker_attributes__service_worker_attributes_competences__competence=1))

CapacityContribution.objects.filter(Q(service_worker_attributes__service_worker_attributes_competences__competence=2))

Each gives me a queryset contaiting an object with id=3886,

but doing:

CapacityContribution.objects.filter(Q(service_worker_attributes__service_worker_attributes_competences__competence=1) & Q(service_worker_attributes__service_worker_attributes_competences__competence=2))

gives me an empty queryset

#

The models in my example are a bit different, but I think the same logic should apply

high dove
#

It can help to look at the resulting SQL from those two queries. You can see it via

qs = CapacityContribution.objects.filter(...)
print(qs.query)