我想从django查询中提取一些特定的列
型号.py
class table
id = models.IntegerField(primaryKey= True)
date = models.DatetimeField()
address = models.CharField(max_length=50)
city = models.CharField(max_length=20)
cityid = models.IntegerField(20)
这是我目前用于查询的内容
obj = table.objects.filter(date__range(start,end)).values('id','date','address','city','date').annotate(count= Count('cityid')).order_by('date','-count')
我希望有一个类似于这个的SQL查询
select DATE(date), id,address,city, COUNT(cityid) as count from table where date between "start" and "end" group by DATE(date), address,id, city order by DATE(date) ASC,count DESC;
至少在Django 1.10.5中,您可以使用这样的东西,而不需要extra
和RawSQL
:
from django.db.models.functions import Cast
from django.db.models.fields import DateField
table.objects.annotate(date_only=Cast('date', DateField()))
对于筛选,可以使用date
查找(https://docs.djangoproject.com/en/1.11/ref/models/querysets/#date):
table.objects.filter(date__date__range=(start, end))
对于以下情况。
select DATE(date), id,address,city, COUNT(cityid) as count from table where date between "start" and "end" group by DATE(date), address,id, city order by DATE(date) ASC,count DESC;
您可以在可以实现DB函数的地方使用extra。
Table.objects.filter(date__range(start,end)).extra(select={'date':'DATE(date)','count':'COUNT(cityid)'}).values('date','id','address_city').order_by('date')
希望它能帮助你。
谢谢。