如何将查询转换为Django ORM查询

Question

我试图弄清楚如何将下面的查询转换成djangonorm查询。 我在windows上使用django2.1python3.5，我试图对Oracle数据库运行查询

SELECT m.msg_no, q.seq, To_Char(m.date_created,'yyyy/mm/dd hh24:mi:ss'), To_Char(SYSDATE,'yyyy/mm/dd hh24:mi:ss'), q.name, Round((round(SYSDATE - m.date_created,6)*1440),1) AS msg_age  
FROM message m
INNER JOIN queue q ON m.queue = q.seq
WHERE m.date_created >= (SYSDATE - 13/24)
AND m.status1 = 0 
AND m.direction = 0;

我有两个模型，想知道如何最好，表Message和Queue是通过Queue主键SEQ和Message外键Queue联系起来的，所以SEQ==Queue在Message表中：

from django.db import models

# Create your models here.
class Queue(models.Model):
  SEQ = models.IntegerField(primary_key=True,)
  NAME = models.CharField(max_length = 50)
  INPUT_DRIVER = models.IntegerField()
  OUTPUT_DRIVER = models.IntegerField()
  DIRECTION = models.IntegerField()
  ORIGINATOR = models.IntegerField()
  DESTINATOR = models.IntegerField()
  PRIORITY = models.IntegerField()
  INPUTEXEC = models.CharField(max_length = 100)
  OUTPUTEXEC = models.CharField(max_length = 100)
  USERID = models.CharField(max_length = 8)
  TMSTAMP = models.DateTimeField(max_length = 7)
  QUEUE_TYPE = models.CharField(max_length = 16)

  class Meta:
      db_table = "QUEUE"

# 
class Message(models.Model):
  MSG_NO = models.IntegerField(primary_key=True,)
  MSG_TYPE = models.IntegerField()
  DIRECTION = models.IntegerField()
  SESSION_NO = models.IntegerField()
  SEQUENCE_NO = models.IntegerField()
  REF_SESSION = models.IntegerField()
  REF_SEQUENCE = models.IntegerField()
  ACKTIME = models.DateTimeField(max_length = 7)
  ACKNAKSTATUS = models.IntegerField()
  PRIORITY = models.CharField(max_length = 1)
  DELIVMONITOR = models.IntegerField()
  OBSOLESCENCE = models.IntegerField()
  DISPOSITION = models.IntegerField()
  TRAILER = models.IntegerField()
  BYPASSED = models.IntegerField()
  RESPONSE_QUEUE = models.IntegerField()
  SOURCE_QUEUE = models.IntegerField()
  QUEUE = models.IntegerField()
  QUEUE_PRIORITY = models.IntegerField()
  DATE_CREATED = models.DateTimeField(max_length = 7)
  DATE_ROUTED = models.DateTimeField(max_length = 7)
  INPUT_FILE = models.IntegerField()
  OUTPUT_FILE = models.IntegerField()
  STATUS1 = models.IntegerField()
  STATUS2 = models.IntegerField()
  STATUS3 = models.IntegerField()
  USERID = models.CharField(max_length = 8)
  TMSTAMP = models.DateTimeField(max_length = 7)

  class Meta:
      db_table = "MESSAGE"

Answer 1

如果要使用Djangos ORM构建该查询，应该使用relationship fields。你知道吗

首先，我建议您将字段改为：

QUEUE = models.ForeignKey(to=Queue, on_delete=models.PROTECT)

然后试着这样做：

import datetime
from django.utils import timezone as tz

now = tz.now()
filter_date = now - datetime.timedelta(hours=...something...)

qs = Message.objects.filter(
    STATUS1=0, DIRECTION=0, DATE_CREATED__gte=filter_date)

你可能想看看^{}。你知道吗

这个问题还需要仔细考虑，以符合你的确切要求，但我的答案应该是正确的，但你在正确的路线上找到它。你知道吗