ORDER BY not allowed in subqueries of compound statements

Using Django Using the ORM
1

I am trying to union 2 querysets having only certain columns, but facing error. For example, have given below the example with only 1 column.

Didn’t even use the Order_by clause, still Django shell environment throws error ‘django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.’

What is the exact syntax for union of 2 or more querysets? - Thank you!

**********************************************************************
** Visual Studio 2022 Developer PowerShell v17.2.3
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
PS D:\IT\Projects\Django\DevApp> python manage.py shell
Python 3.10.4 (tags/v3.10.4:9d38120, Mar 23 2022, 23:13:41) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from Employee.models import *
>>> Dlists = DList.objects.values_list('List_ID')
>>> HLists = HList.objects.values_list('List_ID')
>>> Dlists.union(HLists)
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\query.py", line 370, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\query.py", line 376, in __len__
    self._fetch_all()
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\query.py", line 1866, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\query.py", line 242, in __iter__
    return compiler.results_iter(
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\sql\compiler.py", line 1346, in results_iter
    results = self.execute_sql(
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\sql\compiler.py", line 1382, in execute_sql
    sql, params = self.as_sql()
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\sql\compiler.py", line 605, in as_sql
    result, params = self.get_combinator_sql(
  File "F:\Program Files\Python310\lib\site-packages\django\db\models\sql\compiler.py", line 520, in get_combinator_sql
    raise DatabaseError(
django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.
2

The values_list function doesn’t return a queryset - it returns a list. If you only want a list from the union of two queryset, you need to do the union first, then call values_list on that result.

3

But Ken sir,
I did follow as per Django documentation for union

There they are doing that values_list individually for each queryset.

4

You are quite correct, I missed that!

5

Are you using a custom or customized default manager for either of those classes? What does the Meta class look like for them? Have you done any other customization or modifications of the Model classes?

6

I’ll sure come back to this, as I dropped concept of using union for a while and updated soln with single queryset.

7

It’s probably too late but; I think the issue stems from the SQlite database because similar queries executes properly when I switched to a more robust DB like postgres.

8

ORDER BY not allowed in subqueries of compound statements.
class Movies(models.Model):
name = models.CharField(max_length=31,unique=True)
release_date = models.DateField()
driectory = models.CharField(max_length=20)
genere = models.CharField(max_length=20)
ratings = models.IntegerField()

  class Meta:
        db_table= 'Movie'
        ordering= ['name','release_date',]
        verbose_name_plural='Movies'

class MoviesList(models.Model):
name = models.CharField(max_length=31,unique=True)
release_date = models.DateField()
driectory = models.CharField(max_length=20)
genere = models.CharField(max_length=20)
ratings = models.IntegerField()

  class Meta:
        db_table= 'MovieList'
        ordering= ['name','release_date',]
        verbose_name_plural='MoviesLits'

movies5 = Movies.objects.all().union(MoviesList.objects.all())
facing same error

1 Like