由子查询排序的Django主查询

Question

我想让主查询集按子查询列表排序。

模型之间的关系可以简单表示如下:

class Firm(models.Model):
cik = models.IntegerField(primary_key=True)
class Stock(models.Model):
ticker = models.CharField(max_length=5,primary_key=True)
firm = models.ForeignKey(Firm, on_delete=models.CASCADE,null=True)
class File(models.Model):
accNumber = models.IntegerField(primary_key=True)
firm = models.ForeignKey(Firm, on_delete=models.SET_NULL, null=True)

我要查询的内容:

SELECT ticker
FROM Stock
WHERE firm_id = 
( SELECT firm_id, count(*) as total 
FROM File 
WHERE created_at >yesterday AND created at < today
GROUP BY firm_id 
ORDER BY total )

我使用django.db.models.Subquery来创建上面的嵌套SQL。Django代码:

firm_list = File.objects
.filter(Q(created_at__gte=yesterday)|Q(created_at__lte=today))
.values('firm_id')
.annotate(total=Count('firm_id'))
.order_by('-total')
ticker_list = Stock.objects
.filter(firm_id__in=Subquery(firm_list.values('firm_id')))
.only('ticker')

我想让股票清单按实盘顺序排列。确定列表顺序如下

>>>firm_list.values('firm_id')
<QuerySet [{'firm_id': 1750}, {'firm_id': 1800}, {'firm_id': 4977}, {'firm_id': 3453}]>

，但是股票列表不是按子查询排序的。

>>> ticker_list.values('firm_id')
<QuerySet [{'firm_id': 1750}, {'firm_id': 1800}, {'firm_id': 3453}, {'firm_id': 4977}]>

如何使主查询按子查询列表顺序排序??我使用sqlite数据库。谢谢你。

Answer 1

我用django.db.models中的Case, When来解。

firm_list = File.objects
.filter(Q(created_at__gte=yesterday)|Q(created_at__lte=today))
.values('firm_id')
.annotate(total=Count('firm_id'))
.order_by('-total')
.values_list('firm_id',flat=True)
preserved = Case(*[When(firm_id=firm_id,then=pos) for pos, firm_id in enumerate(firm_list)])
ticker_list = Stock.objects
.filter(firm_id__in=Subquery(firm_list.values('firm_id')))
.order_by(preserved)