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

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:

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

CodePudding user response：

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.

CodePudding user response：

@nigel239 I think you don't need to use _set in filtering reverse relationship, you just use model name. In addition you can make more with Django ORM. For first question it goes like:

Property.objects.filter(area__amenities__name="Television")
        .annotate(tv_ocurance=Count('area__amenities', filter=Q(area__amenities__name="Television")))
        .filter(tv_ocurance__gte=3)

you can do exactly the same for second query.

UPDATE:

As @nigel239 suggested it can be done like:

Property.objects.filter(area__amenities__name="Television")\
        .annotate(tv_count=Count('area__amenities')\
        .filter(tv_count__gte=3)

Or my way:

 Property.objects\
        .annotate(tv_ocurance=Count('area__amenities', filter=Q(area__amenities__name="Television")))\
        .filter(tv_ocurance__gte=3)