CODEWITHSUNDEEP
djangodjango-queryset
Seckinyilmaz
Seckinyilmaz

Reputation: 83

Django: Order_by combined two columns

I have following query; There are two columns (dateEmploymentRD and dateTerminationRD).

qsPersonnel = qsPersonnel.filter(Q(dateEmploymentRD__isnull=False), 
                                        Q(dateEmploymentRD__lte=personnelDateMax), 
                                        Q(dateTerminationRD__gte=personnelDateMin) | Q(dateTerminationRD__isnull=True), 
                                        Q(dateTerminationRD__lte=personnelDateMax) | Q(dateTerminationRD__isnull=True)).order_by('dateEmploymentRD','dateTerminationRD')

I would like to sort that queryset with combined two coulmns (dateEmploymentRD and dateTerminationRD).

But my query is ordering firstly 'dateEmployementRD' and than ordering by 'dateTerminationRD' as below.

How can I order my query by combined two columns?

Edit-1: Added views.py

def index(request):
    qsPersonnel = filterPersonnel(request)
    qsPersonelgraduatelevel= qsPersonnel.values('graduateLevel',).annotate(Count('graduateLevel')).order_by()
    qsPersoneltask = qsPersonnel.values('task',).annotate(Count('task')).order_by()
    qsPersonelgender = qsPersonnel.values('gender',).annotate(Count('gender')).order_by()
    
    personnelDateMin = request.GET.get('personnelDateMin')
    personnelDateMax = request.GET.get('personnelDateMax')
    personnelDateMaxMin = dtt.strptime(personnelDateMin,'%Y-%m-%d').date()
    personnelDateMaxDate = dtt.strptime(personnelDateMax,'%Y-%m-%d').date()

    qsPersonnelsum = filterPersonnelsum(request)
    
    personnelChart = {}
    qsPersonnelsumbase = filterPersonnelsum(request).aggregate(Count('id'))
    b = qsPersonnelsumbase['id__count']

    personnelChart[personnelDateMin] = b
    


    for i in qsPersonnel:
        if i.dateTerminationRD and i.dateTerminationRD < personnelDateMaxDate:
            b -= 1
            personnelChart[str(i.dateTerminationRD)] = b
            
        elif i.dateEmploymentRD and i.dateEmploymentRD > personnelDateMaxMin:
            b += 1
            personnelChart[str(i.dateEmploymentRD)] = b
    
    personnelChartSorted = dict(sorted(personnelChart.items()))

    context ={
       'qsPersonnel' : qsPersonnel,
       'qsPersonelgraduatelevel' : qsPersonelgraduatelevel,
       'qsPersoneltask' : qsPersoneltask,
       'qsPersonelgender' : qsPersonelgender,
    
    }
    
    return render(request, 'index.html', context)

Upvotes: 0

Views: 261

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32294

To order by the earliest of two columns you have to add an annotation that calculates the earliest of the two using Least, then you can order by the annotation

import datetime
from django.db.models.functions import Least, Coalesce

qsPersonnel = qsPersonnel.annotate(
    earliest=Coalesce(
        Least('dateEmployementRD', 'dateTerminationRD'),
        datetime.datetime.max
    )
).order_by('earliest')

Upvotes: 2

Related Questions