Django Subquery in SQL FROM part

Question

The Django Documentation shows how to create a subquery in the SELECT part of the SQL query, but how to create a subquery in the FROM part? i.e:

SELECT    ...
FROM (
    SELECT    ...
    FROM      ...
    WHERE     ...
    GROUP BY  ...
)
WHERE     ...
ORDER BY  ...

Here is a specific example in case this helps, but if you can answer the general question above without this example, please do.

---

If we have the models:

class MyModel(models.Model):
    field1 = models.CharField()
    other_model = models.ForeignKey(OtherModel)


class OtherModel(models.Model):
    field2 = models.CharField()
    field3 = models.CharField()
    active = models.BooleanField()

Let's say I want to count for each my_model.field1 value the number of OtherModel.field3 values where there are some instances my_model, other_model with my_model.other_model == other_model and other_model.active == True So I would like to have a subquery where I group by field1 and field3 and count the actives:

sub_query = MyModels.values('field1', 'othermodel__field3').annotate(actives=Count(Case(When(othermodel__active=True, then=1))))

And then a main query where I group the results of the first query by field1 and count them per group:

main_query = qub_query.values('field1').annotate(active_combinations=Count('field1', filter=Q(actives__gt=0)))

But this is interpreted in a different way and does not work like this.

question from:https://stackoverflow.com/questions/65906299/django-subquery-in-sql-from-part

Answer 1

Waitting for answers