K
K
kvarel2022-04-03 21:12:38
Django
kvarel, 2022-04-03 21:12:38

How to compose a query from 3 tables in Django ORM?

There is a training database from 3 tables. Country, clubs and fans of clubs.

class Country(models.Model):
    title = models.CharField(max_length=20)
    strength = models.IntegerField('Численность')

    def __str__(self):
        return self.title


class Club(models.Model):
    title = models.CharField(max_length=20)
    country = models.ForeignKey(Country, on_delete=models.CASCADE)

    def __str__(self):
        return self.title


class Fun(models.Model):
    name = models.CharField(max_length=15)
    club = models.ForeignKey(Club, on_delete=models.CASCADE)

    def __str__(self):
        return self.name


It is necessary to compose a queryset of instances of the Country model, annotated with the number of clubs with the number of fun greater than n.

If we omit the requirement to create a queryset, then it turns out to be solved with the following query:
Club.objects.annotate(c=Count('fun')).filter(c__gt=<n>).annotate(total=Count('title', distinct=True)).values('country__title', 'total')

If you try to enter all the same with getting a queryset, then I came up with just such a mess:
we make a subquery:
sub = Club.objects.filter(country=OuterRef('pk')).annotate(c=Count('fun')).filter(c__gt=<n>).annotate(total=Count('title', distinct=True)).values('total')

Paste in the main query
x = Country.objects.annotate(result=Subquery(sub[:1]))


It seems to work. Is there any easier way to implement this? Without Subquery.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question