[Fixed]-Filter objects using foreignkeys

by

0đź‘Ť

âś…

Related manager seems like a feasible option.

Django offers a powerful and intuitive way to “follow” relationships
in lookups, taking care of the SQL JOINs for you automatically, behind
the scenes. To span a relationship, just use the field name of related
fields across models, separated by double underscores, until you get
to the field you want.

It works backwards, too. To refer to a “reverse” relationship, just use the lowercase name of the model.

parents = Parent.objects.filter(kid__name=X,kid__age__gte=Y,kid__city=Z)

PS: I have not tested this code. The intention is to give a suggestion on approach.

Edit 1: Addressing the exclude exception as pointed in the comments. Link to Django documentation. Refer to the note in this section

Exclude behavior is different than filter. Exclude in related manager doesn’t use a combination of conditions instead excludes both eg. Parent.objects.exclude(kid__name=X,kid__city=Z) will exclude kids with name X and kids from city Z instead of kids with name X who are from city Z

Django suggested approach is:

Parent.objects.exclude(kid__in=Kid.objects.filter(name=X,city=Z))
👤Aditya

1đź‘Ť

You can do it in reversed logic. Firstly you need to change age to IntegerField otherwise you wouldn’t be able to compare it’s values

class Kid(models.Model):
    family = models.ForeignKey(Parent)
    title = models.CharField(max_length=250)
    age = models.IntegerField()
    city = models.CharField(choices=cities)

Then you can filter all kids that comply with your filter and get ids of parents to filter on later

filter_ids = Kid.objects.filter(name=X, age__gte=Z, city=Y).values_list('parents_id', flat=True).distinct()
exclude_ids = Kid.objects.filter(name=X, age__lt=Z, city=Y).values_list('parents_id', flat=True).distinct()

parents = Parent.objects.filter(id__in=filter_ids).exclude(id__in=exclude_ids)

Answering comment
Same logic you firstly fillter all parents with such kids, then you exclude parents that have other kids.

filter_ids = Kid.objects.filter(my_pattern).values_list('parents_id', flat=True).distinct()
exclude_ids = Kid.objects.exclude(my_pattern).values_list('parents_id', flat=True).distinct()

parents = Parent.objects.filter(id__in=filter_ids).exclude(id__in=exclude_ids)
👤Sardorbek Imomaliev

Leave a comment