Is it possible to create a QuerySet from a model and determine in the sample a field with all values ​​equal to "0.0" without losing the opportunity to work with the query object and its fields as with a QuerySet? After combining, I want to make a "UNION ALL" with a different sample and calculate the sum of the fields 'plan' and 'fact'.

What other solutions besides the RAW request should be considered?

from django.db.models import Model, ForeignKey, TextField, DecimalField, DateField, CASCADE class ModelValueMixin(Model): date = DateField(auto_now=True) value = DecimalField() classifier = ForeignKey('Catalog', on_delete=CASCADE) class Meta: abstract = True class ModelValue1(ModelValueMixin): pass class ModelValue2(ModelValueMixin): pass class Catalog(Model): code = TextField() name = TextField()

An example of how I see it with SQLAlchemy:

 session.query( ModelValues1.value.label('plan'), literal_column('0').label('fact'), ModelCatalog.code.label('code'), ModelCatalog.name.label('name') ).select_from(ModelValues1).outerjoin(ModelCatalog)

And directly in SQL, I see it like this:

 SELECT "catalog"."id", "catalog"."code", "catalog"."full_name", "values1"."value" AS "plan", (0) AS "fact" FROM "catalog" LEFT OUTER JOIN "values1" ON (catalog."id" = "values1"."catalog_id")

The final goal is to get a summary table with the fields "code", "name", "sum_plan", "sum_fact."

  • Show the ModelValues1 and ModelCatalog . And better tell in more detail what result you want to achieve. And that is the persistent feeling that you reinvent the wheel. - Sergey Gornostaev
  • Added by. I'm not sure that I am doing it right, but I will be glad if you recommend another way for Django. - Tihon
  • So, you have directories, each of which may have a variable number of some value. What is this for? What do you want to count? - Sergey Gornostaev
  • The catalog with the name and code of the element, as well as two tables in one of which are planned values, and the second - actual values. And I want to get - a pivot table with the total values ​​of the plan and fact for each catalog item. - Tihon

2 answers 2

You need aggregation and annotations . It should make something like

 from django.db.models import Sum catalogs = Catalog.objects.annotate(plan=Sum(modelvalue1_set__value), fact=Sum(modelvalue2_set__value)).all() for catalog in catalogs: print("{}: {}/{}".format(catalog.name, catalog.plan, catalog.fact))
  • Right up to the moment when the models with the values ​​of one directory. I can have them up to ten. That's why I reinvent the wheel. - Tihon
  • The classifier field is a ForeignKey, it can refer only to one directory. - Sergey Gornostaev
  • I - incorrectly explained. There may be many fields of the form classifier_1 , classifier_2 , classifier_3 , etc. Therefore, I first make two samples, then combine them and count the sum of the values ​​of the combined samples. - Tihon
  • The request that is specified in your response is converted to something like the following SELECT "app_classifier"."id", "app_classifier"."short_name", "app_classifier"."full_name", "app_classifier"."code" CAST(SUM("app_plan"."value") AS NUMERIC) AS "plan", CAST(SUM("app_fact"."value") AS NUMERIC) AS "fact" FROM "app_classifier" INNER JOIN "app_plan" ON ("app_classifier"."id" = "app_plan"."steward_id") INNER JOIN "app_fact" ON ("app_classifier"."id" = "app_fact"."steward_id") GROUP BY "app_classifier"."id", "app_classifier"."short_name", "app_classifier"."full_name", "app_classifier"."code" - Tihon
  • It works, unfortunately - slowly. - Tihon

Added SQLAlchemy to the project and rewrote complex queries on SQLAlchemy Core.

The question decided - so . It was possible to achieve the request execution time in less than 100 ms.