How can I filter Django models using field lookups that span relationships and aggregation functions?

Question

I have a set of four models representing rental properties.

The Property model stores the property's name and who created it.

class Property(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    creator = models.ForeignKey(User, related_name='creator', on_delete=models.PROTECT)
    name = models.CharField(max_length=100)

A Property can have many Area instances throug a ForeignKey relationship. An Area has an AreaType through a ForeignKey relationship as well, and many Amenities through a ManyToManyField.

class Area(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    type = models.ForeignKey(AreaType, on_delete=models.PROTECT)
    property = models.ForeignKey(Property, on_delete=models.PROTECT)
    amenities = models.ManyToManyField(Amenity, null=True, blank=True)

class AreaType(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=100)


class Amenity(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=100)

I am familiar with field lookups that do and do not span relationships, as well as filtering through kwargs.

What I am not familiar with is combining field lookups and aggregation functions. And therefore, given the models above, I would like to:

1. Retrieve all Properties that have at least 3 Amenities with the name 'Television'.
2. Retrieve all Properties that have at least 2 Areas with an AreaType name of 'Bathroom'.

Answer 1

To retrieve all properties with ammenities with name Television:

Big thanks to @Waldemar Podsiadło, I misunderstood the question. He provided the correct answer for counting tv's.

Property.objects.filter(area_set__amenities__name="Television")\
    .annotate(tv_count=models.Count('area_set__amenities')\
    .filter(tv_count__gte=3))

Should retrieve all properties relating to ammenities with name Television, and with a count greater than or equal to 3.

You did not define a related_name, so therefore the reverse of area will be area_set. From there on, you have the properties available to you as fields, so you can just continue to filter from that.

To access a field in the query, separate it with double underscores __, this also works with expressions, like __count, and __sum (most of the time).

You can figure out what query expressions you can use with:

Property._meta.fields.get_field('your_field_name').get_lookups()

A link to the django-docs to learn more about querysets, and filtering.

A link to the django-docs to learn more about filtering.

Edit: Kwargs example:

def filter_my_queryset(tv_count: dict, *args, **kwargs):
    Property.objects.filter(**kwargs)\
        .annotate(tv_count=models.Count(*args)\
        .filter(**tv_count)) # Need a second dict, unless you always need a gte filter. Then you can dynamically do it.

Or, you could dynamically get the amenities, by comparing the last part of the expression with the fields on a model; if its not a field, cut it out. In this example that should cut out area_set__amenities__name to area_set__amenities

You can get a list of fields from the model with:

field_name_list = []
for i in mymodel._meta.fields:
    field_name_list.append(i.name)

Or alternatively; use hasattr() to check if a field exists. This might give false positives though, since any attributes count toward it.

Sidenote:

Read this doc, I think you'll find it very useful if you need to pack a lot of queries into functions. Custom QuerySets are great!

https://docs.djangoproject.com/en/4.1/topics/db/managers/