Django annotate count for another queryset

Question

Models:

class Regions(models.Model):
    name = models.CharField(max_length=255, unique=True)


class Owners(models.Model):
    name = models.CharField(max_length=255, null=False, unique=True)
    url = models.URLField(null=True)


class Lands(models.Model):
    region = models.ForeignKey(Regions, on_delete=models.CASCADE)
    owner = models.ForeignKey(Owners, on_delete=models.PROTECT, null=True)
    description = models.TextField(max_length=2000, null=True)


class LandChangeHistory(models.Model):
    land = models.ForeignKey(Lands, on_delete=models.CASCADE, null=False, related_name='lands')
    price = models.IntegerField()
    size = models.IntegerField()
    date_added = models.DateField(auto_now_add=True)

Queryset that works but i need it to be annotated in another queryset somehow:

lands_in_region = Lands.objects.values('region__name').annotate(count=Count('region_id'))

returns for example:

{'region__name': 'New York', 'count': 3}, {'region__name': 'Chicago', 'count': 2}

In the 2nd queryset i need count of lands available in region. But instead of real count, i always get count = 1. How to combine them? Im pretty sure i could do it in raw sql by joining two tables on field "region__id", but dont know how to do it in django orm.

f = LandFilter(request.GET, queryset=LandChangeHistory.objects.all()
                           .select_related('land', 'land__region', 'land__owner')
                           .annotate(usd_per_size=ExpressionWrapper(F('price') * 1.0 / F('size'), output_field=FloatField(max_length=3)))
                           .annotate(count=Count('land__region_id'))
                           )

For example. If it returns:

land1 | 100$ | 100m2 | New York

land2 | 105$ | 105m2 | New York

land3 | 102$ | 102m2 | Chicago

i need 1 more column, that counts for each land how many NewYork's and Chicago's are there

land1 | 100$ | 100m2 | New York | 2

land2 | 105$ | 105m2 | New York | 2

land3 | 102$ | 102m2 | Chicago | 1

score 0 · Accepted Answer · answered Sep 22 '22 at 10:37

This worked for me. Hope helps somebody.

First i tried to simply call Count() method right after filter, but that breaks query, since it tries to get data from DB immediately. But that was the correct way to think, so i added count annotate and selected it and it worked.

 f = LandFilter(request.GET, queryset=LandChangeHistory.objects.all()
 .select_related('land', 'land__region', 'land__owner')
 .annotate(usd_per_size=ExpressionWrapper(F('price') * 1.0 / F('size'), output_field=FloatField(max_length=3)))        
    
 .annotate(count=Subquery(
                     Lands.objects.filter(region_id=OuterRef('land__region_id'))
                     .values('region_id')
                     .annotate(count=Count('pk'))
                     .values('count')))
                     )

Django annotate count for another queryset

1 Answers1