Django Queryset Filter Missing Quotes

I have a list and I want to filter my Queryset when any of these items is found in a foreign table’s non-primary key ‘test’. So I write something like this:

test_list = ['test1', 'test2', 'test3', 'test4', 'test5']
return cls.objects.filter(reduce(lambda x, y: x | y, [models.Q(next_task__test = item) for item in test_list]))[:20]

This returns an empty list. When I look at the SQL query it generated, I get:

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN (test1, test2, test3, test4, test5) LIMIT 20;

Whereas what it should have been is this:

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN ('test1', 'test2', 'test3', 'test4', 'test5') LIMIT 20;

Without the quotes, SQLite3 believes those are column names, and does not return anything. When I manually add the quotes and execute an SQLite3 query on the table without Django at all, I get the desired results. How do I make Django issue the query correctly?

Answer

This issue is quite interesting, it seems to happen with SQLite only. It’s known here: https://code.djangoproject.com/ticket/14091 and in the docs.

So basically the query might not be wrong, but when you get the query back with Django it looks wrong:

>>> test_list = ['test1', 'test2', 'test3', 'test4', 'test5']
>>> cls.objects.filter(next_task__test__in=test_list).query.__str__()

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN (test1, test2, test3, test4, test5);

Work around: if you really think the query is wrong, then provide more quote for the list, something like:

>>> test_list = ["'test1'", "'test2'", "'test3'", "'test4'", "'test5'"]
>>> cls.objects.filter(next_task__test__in=test_list).query.__str__()

SELECT ...
FROM ...
WHERE "job"."next_task_id" IN ('test1', 'test2', 'test3', 'test4', 'test5');

I would rely on the standard one anyway, the work around above is too hackish.