0

The raw query itself is correct and I am able to get retrieve the raw query set from the db. I need to convert this into query set for further processing and I am facing below error.

Creating corresponding django query was hard for me and that is why I created SQL query, got the raw query set and now attempting to convert it to query set for further processing.

I have changed django model names and table names for anonymity.

Here is the output of what I tried in django shell. The "test3.value" will be dynamically set and I am doing that in the raw query statement.

from django.db.models.expressions import RawSQL
from xyz.models import *
value = '1.2.3.4'
queryset = Test1.objects.filter(id__in=RawSQL("SELECT DISTINCT ON (test1.start_time, test1.id) test1.id, test1.name, test1.start_time FROM test1 WHERE EXISTS (SELECT * FROM test2 JOIN test3 ON test2.test3_id = test3.id AND test3.value = {} JOIN test4 ON test2.test4_id = test4.id AND test4.test1_id = test1.id) ORDER BY test1.start_time DESC".format(value)))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
TypeError: __init__() missing 1 required positional argument: 'params'

For readability I have formatted the raw sql query used above.

SELECT
  DISTINCT ON (test1.start_time, test1.id)
  test1.id,
  test1.name,
  test1.start_time
FROM
  test1
WHERE
  EXISTS (
    SELECT
      *
    FROM
      test2
      JOIN test3 ON test2.test3_id = test3.id
      AND test3.value = {}
      JOIN test4 ON test2.test4_id = test4.id
      AND test4.test1_id = test1.id
  )
ORDER BY
  test1.start_time DESC.format(value)
Sandesh Veerapur
  • 51
  • 8
  • This query is successful. rawquery=Test1.objects.raw("SELECT DISTINCT ON (test1.start_time, test1.id) test1.id, test1.name, test1.start_time FROM test1 WHERE EXISTS (SELECT * FROM test2 JOIN test3 ON test2.test3_id = test3.id AND test3.value = {} JOIN test4 ON test2.test4_id = test4.id AND test4.test1_id = test1.id) ORDER BY test1.start_time DESC".format(value))) – Sandesh Veerapur Apr 07 '21 at 22:29
  • You're using RawSQL wrong. Read the documentation: https://docs.djangoproject.com/en/3.1/ref/models/expressions/#raw-sql-expressions – Aleksandar Varicak Apr 07 '21 at 22:55
  • I used this article https://stackoverflow.com/questions/48288076/convert-django-rawqueryset-to-queryset as reference and wrote my code above. – Sandesh Veerapur Apr 07 '21 at 23:04
  • Is "queryset" a module in django? I get error "NameError: name 'queryset' is not defined" for "queryset" when I execute "queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))" – Sandesh Veerapur Apr 07 '21 at 23:09
  • The query needs additional parameter "params". Here it is. queryset = Test1.objects.filter(id__in=RawSQL("SELECT DISTINCT ON (test1.start_time, test1.id) test1.id, test1.name, test1.start_time FROM test1 WHERE EXISTS (SELECT * FROM test2 JOIN test3 ON test2.test3_id = test3.id AND test3.value = {} JOIN test4 ON test2.test4_id = test4.id AND test4.test1_id = test1.id) ORDER BY test1.start_time DESC", params=[value])) – Sandesh Veerapur Apr 08 '21 at 00:36

0 Answers0