Subqueries via related fields in django.db

In the project there are models Family ( Family ), Category ( Category ) and Subcategory ( Subcategory ):

 class Family(models.Model): name = models.CharField(max_length=100) class Category(models.Model): name = models.CharField(max_length=100) class Trend(models.Model): name = models.CharField(max_length=100) area = models.ForeignKey(Area, related_name='subcategories') families = models.ManyToManyField(Family, related_name='subcategories') 

Thus, the database structure looks like this:

It turns out that there is a single category and subcategories in the whole system. At the same time, families can disable subcategories for themselves (if there is a connection between Family and Subcategory , then the subcategory for the family is active). I try to display all categories and subcategories by adding an additional active parameter to the subcategories. On pure sql query looks like this:

 SELECT cat.*, subcat.*, CASE WHEN tr.id IN ( SELECT tr.id FROM family fam INNER JOIN subcategoryfamilyrel rel ON fam.id = rel.family_id INNER JOIN subcategory subcat1 ON rel.trend_id = subcat1.id WHERE fam.id = {family.id} ) THEN 1 ELSE 0 END AS active FROM subcategory subcat INNER JOIN category cat ON subcat.category_id = cat.id; 

But at the same time I can not figure out how to properly make a request through the ORM . So far I have reached the point that I generate a queryset , where Category :

 Subcategory.objects.annotate( active=Case( When(id__in=profile.family.subcategories.all().values('id'), then=True), default=Value(False), output_field=BooleanField() ) ) 

And by passing the queryset to rest_framework.Serializer the output is the following:

 [ { "id": 1, "name": "Русский язык", "active": true, "area": { "id": 1, "name": "Школа", "color": "123456" } }, { "id": 2, "name": "Математика", "active": , "area": { "id": 1, "name": "Школа", "color": "123456" } }, { "id": 6, "name": "Уборка", "active": true, "area": { "id": 2, "name": "Дом", "color": "ABCDEF" } } ] 

And I would like to receive this:

 [ { "id": 1, "name": "Школа", "color": "123456", "subcategories": [ { "id": 1, "name": "Русский язык", "active": true }, { "id": 2, "name": "Математика", "active": false } ] }, { "id": 2, "name": "Дом", "color": "ABCDEF", "subcategories": [ { "id": 3, "name": "Уборка", "active": true } ] } ] 

For now, I see two solutions:

1. Create a query, starting from Category , and add the active field to the associated Subcategory ;
2. An existing query with subcategories to merge under general categories.