rawsql等价django queryset

Question

我想编写django queryset，它相当于下面的查询，在db中有一个命中。现在，我正在使用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);

Ex1：

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和子查询来实现它。就像这样：

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)

FYI:从1.11版本的Django中可以获得OuterRef和Subquery

Answer 2

如果您的模型名为Table1，请尝试以下操作。

Table1.objects.get(pk=Table1.objects.filter(company_id=3, total_employees_isnull=False).latest('year').first().id)

这可能是分局的一次命中。

但是如果.first()不匹配的话。最好是这样：

filter_item = Table1.objects.filter(company_id=3, total_employees_isnull=False).latest('year').first()
if filter_item:
   return Table1.objects.get(pk=filter_item.id)