Better Alternate instead of using chained union queries in Django ORM

Question

I needed to achieve something like this in Django ORM :

(SELECT * FROM `stats` WHERE MODE = 1 ORDER BY DATE DESC LIMIT 2) 
UNION
(SELECT * FROM `stats` WHERE MODE = 2 ORDER BY DATE DESC LIMIT 2)     
UNION                                                                       
(SELECT * FROM `stats` WHERE MODE = 3 ORDER BY DATE DESC LIMIT 2)
UNION                                                                         
(SELECT * FROM `stats` WHERE MODE = 6 ORDER BY DATE DESC LIMIT 2) 
UNION
(SELECT * FROM `stats` WHERE MODE = 5 AND is_completed != 3 ORDER BY DATE DESC)                                                                           
# mode 5 can return more than 100 records so NO LIMIT here 

for which i wrote this :

query_run_now_job_ids = Stats.objects.filter(mode=5).exclude(is_completed=3).order_by('-date')
list_of_active_job_ids = Stats.objects.filter(mode=1).order_by('-date')[:2].union(
                            Stats.objects.filter(mode=2).order_by('-date')[:2],
                            Stats.objects.filter(mode=3).order_by('-date')[:2],
                            Stats.objects.filter(mode=6).order_by('-date')[:2],
                            query_run_now_job_ids)

but somehow list_of_active_job_ids returned is unordered i.e list_of_active_job_ids.ordered returns False due to which when this query is passed to Paginator class it gives :

UnorderedObjectListWarning: 
Pagination may yield inconsistent results with an unordered object_list  

I have already set ordering in class Meta in models.py

class Meta:
        ordering = ['-date']

Without paginator query works fine and page loads but using paginator , view never loads it keeps on loading .

Is there any better alternate for achieving this without using chain of union .

So I tried another alternate for above mysql query but i'm stuck in another problem to write up condition for mode = 5 in this query :

SELECT  
    MODE ,
    SUBSTRING_INDEX(GROUP_CONCAT( `job_id` SEPARATOR ',' ),',',2) AS job_id_list,
    SUBSTRING_INDEX(GROUP_CONCAT( `total_calculations` SEPARATOR ',' ),',',2) AS total_calculations
FROM `stats`            
ORDER BY DATE DESC 

Even if I was able to write this Query it would lead me to another challenging situation i.e to convert this query for Django ORM .

So why My Query is not ordered even when i have set it in Class Meta .

Also if not this query , Is there any better alternate for achieving this ?

Help would be appreciated ! .

I'm using Python 2.7 and Django 1.11 .

Answer 1

While subqueries may be ordered, the resulting union data is not. You need to explicitly define the ordering.

from django.db import models

def make_query(mode, index):
    return (
        Stats.objects.filter(mode=mode).
        annotate(_sort=models.Value(index, models.IntegerField())).
        order_by('-date')
    )

list_of_active_job_ids = make_query(1, 1)[:2].union(
     make_query(2, 2)[:2],
     make_query(3, 3)[:2],
     make_query(6, 4)[:2],
     make_query(5, 5).exclude(is_completed=3)
).order_by('_sort', '-date')

All I did was add a new, literal value field _sort that has a different value for each subquery and then ordered by it in the final query.The rest of the code is just to reduce duplication. It would have been even cleaner if it wasn't for that mode=6 subquery.