python - rawsql 相当于 django 查询集

Question

我想编写 django 查询集，它相当于下面的查询，在数据库中单击一次。现在我正在使用manager.raw()来执行。

通过annotate，我可以生成内部查询。但我不能在过滤条件中使用它(当我检查 queryset.query 时，它看起来像 ex1)。

select *
from table1
where (company_id, year) in (select company_id, max(year) year
                             from table1
                             where company_id=3
                             and total_employees is not null
                             group by company_id);

例1:

SELECT `table1`.`company_id`, `table1`.`total_employees`
FROM `table1`
WHERE `table1`.`id` = (SELECT U0.`company_id` AS Col1, MAX(U0.`year`) AS `year`
                       FROM `table1` U0
                       WHERE NOT (U0.`total_employees` IS NULL)
                       GROUP BY U0.`company_id`
                       ORDER BY NULL)

型号:

class Table1(models.Model):
    year = models.IntegerField(null=False, validators=[validate_not_null])
    total_employees = models.FloatField(null=True, blank=True)
    company = models.ForeignKey('Company', on_delete=models.CASCADE, related_name='dummy_relation')
    last_modified = models.DateTimeField(auto_now=True)
    updated_by = models.CharField(max_length=100, null=False, default="research")

    class Meta:
        unique_together = ('company', 'year',)

感谢您的回复。

Answer 1

您可以使用OuterRef和 Subquery来实现它。尝试这样:

newest = Table1.objects.filter(company=OuterRef('pk'), total_employees_isnull=False).order_by('-year')
companies = Company.objects.annotate(total_employees=Subquery(newest.values('total_employees')[:1])).annotate(max_year=Subquery(newest.values('year')[:1]))
# these queries will not execute until you call companies. So DB gets hit once

显示值:

# all values
companies.values('id', 'total_employees', 'max_year')

# company three values
company_three_values = companies.filter(id=3).values('id', 'total_employees', 'max_year')

按最大年份过滤:

companies_max = companies.filter(max_year__gte=2018)

仅供引用:OuterRef 和 Subquery 在 Django 1.11 版本中可用