如何在Django视图中获得与该MySQL脚本等效的内容?用户将输入depart_city,arrival_city和travel_date。
这是我的模特儿
class Driver(models.Model):
first_name = models.CharField(max_length=30, null=True, blank=False)
last_name = models.CharField(max_length=30, null=True, blank=False)
class Schedule(models.Model):
depart_time = models.TimeField()
arrive_time = models.TimeField()
class TravelDate(models.Model):
start_date = models.DateField(null = True)
interval = models.IntegerField(null = True)
class Route(models.Model):
depart_city = models.CharField(max_length=50, null=True, blank=False)
arrive_city = models.CharField(max_length=50, null=True, blank=False)
driver = models.ForeignKey(Driver)
schedule = models.ForeignKey(Schedule)
traveldate = models.ForeignKey(TravelDate)
这是我的MySQL脚本。当我在MySQL工作台上运行它时此方法有效,但我不确定如何将其转换为Django Query
SELECT busapp_route.depart_city, busapp_route.arrive_city, busapp_driver.first_name, busapp_schedule.depart_time
FROM (((busapp_route INNER JOIN busapp_driver ON busapp_route.driver_id = busapp_driver.id)
INNER JOIN busapp_schedule ON busapp_route.schedule_id = busapp_schedule.id)
INNER JOIN busapp_traveldate ON busapp_route.traveldate_id = busapp_traveldate.id)
WHERE busapp_route.depart_city='Tropoje' AND busapp_route.arrive_city='Tirane'
AND (DATEDIFF('2017-11-26', busapp_traveldate.start_date) % busapp_traveldate.interval = 0);
最佳答案
Django本机不支持MySQL DATEDIFF。作为解决方法,您可以使用以下方法:
from django.db.models.expressions import RawSQL
routes = Route.objects\
.values('depart_city', 'arrive_city', 'driver__first_name', 'schedule__depart_time', 'traveldate__start_date')\
.annotate(datediff_mod=RawSQL("DATEDIFF(%s, busapp_traveldate.start_date) MOD busapp_traveldate.interval", ('2017-11-26', )))\
.filter(depart_city='Tropoje', arrive_city='Tirane', datediff_mod = 0)
我不使用MySQL,所以无法测试它,但是我很确定它应该可以工作,或者至少可以给您一个实现它的想法。