How to convert a query to django ORM Query

Question

I am trying to figure out how I can convert the following query into a Djangon ORM query. I am using Django 2.1 Python 3.5 on windows, I am trying to run the query against a Oracle database

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;

I have two models, would like to know how to best, the tables Message and Queue are relate by Queue primary key SEQ and Message foreign key QUEUE, so SEQ == QUEUE in Message table:

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"

Side note: I don't think ... = models.DateTimeField(max_length = 7) is correct; the argument max_length usually goes only for CharField. — Ralf
– Ralf, Commented Sep 13, 2018 at 12:12

Ralf · Accepted Answer · 2018-09-13 12:11:33Z

2

If you want to use Djangos ORM to build that query, you should use the relationship fields.

First, I suggest you change the field to this:

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

And then try something like this:

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)

You may want to look into QuerySet.annotate().

This still needs to be tweeked to fit your exact query, but my answer should but you on the right course to figure it out.

answered Sep 13, 2018 at 12:11

Ralf

16.6k4 gold badges50 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Thobs More Over a year ago

The major problem is "m.date_created >= (SYSDATE - 13/24)"

Thobs More Over a year ago

The code above produces a where clause below and it does not run manually: WHERE ("MESSAGE"."DATE_CREATED" >= 2018-09-15 12:22:34.762710 AND "MESSAGE"."DIRECTION" = 0 AND "MESSAGE"."STATUS1" = 0

Thobs More Over a year ago

I need the where clause to have (SYSDATE-3) in place of the date: WHERE ("MESSAGE"."DATE_CREATED" >= (SYSDATE-3) AND "MESSAGE"."DIRECTION" = 0 AND "MESSAGE"."STATUS1" = 0

Thobs More Over a year ago

How do I produce SYSDATE-2 from django's perspective

Ralf Over a year ago

@ThobsMore Is there a meaningful difference between using SYSDATE - 13/24 and tz.now() - datetime.timedelta(hours=13) like I suggested? I think both filter rows that are younger than 13 hours.

|

Collectives™ on Stack Overflow

How to convert a query to django ORM Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related