CODEWITHSUNDEEP
pythondjangosorting
Uri
Uri

Reputation: 3281

How to order a reverse foreign key in Django?

We are using Django 1.4. We have class A which has a class History related to it. There can be many History objects related to any single object of A.

The History class has foreign keys to other classes, such as BusinessUnit.

I want to sort objects of A by the last history object, if any (sorted by History.logged_at).

I tried with order_by('history__business_unit'), but it doesn't work properly. Is it possible to sort like this using the database, or do we have to sort using Python? I prefer to sort using the database if possible.

Upvotes: 5

Views: 2903

Answers (2)

Uri
Uri

Reputation: 3281

Eventually I sorted with Python, using list.sort:

def human_key(key):
    parts = re.split('(\d*\.\d+|\d+)', key.lower())
    return tuple((e.swapcase() if i % 2 == 0 else float(e)) for i, e in enumerate(parts))

if (ordered in ["unregistered_business_unit", "unregistered_operational_unit", "unregistered_date"]):
    a_list = list(a_list)
    for a in a_list:
        a_history = list(a.history.all().order_by('-logged_at'))
        if (len(a_history) > 0):
            a.last_a_history = a_history[0]
        else:
            a.last_a_history = None
    if (ordered == "unregistered_business_unit"):
        a_list.sort(key=lambda a: (a.last_a_history.business_unit.description.lower() if ((a.last_a_history) and (a.last_a_history.business_unit)) else None, a.last_a_history.business_unit.identifier if ((a.last_a_history) and (a.last_a_history.business_unit)) else None, human_key(a.last_a_history.operational_unit.identifier) if ((a.last_a_history) and (a.last_a_history.operational_unit)) else None, a.mac_address), reverse=reverse_flag)
    elif (ordered == "unregistered_operational_unit"):
        a_list.sort(key=lambda a: (human_key(a.last_a_history.operational_unit.identifier) if ((a.last_a_history) and (a.last_a_history.operational_unit)) else None, a.last_a_history.business_unit.description.lower() if ((a.last_a_history) and (a.last_a_history.business_unit)) else None, a.last_a_history.business_unit.identifier if ((a.last_a_history) and (a.last_a_history.business_unit)) else None, a.mac_address), reverse=reverse_flag)
    elif (ordered == "unregistered_date"):
        a_list.sort(key=lambda a: (a.last_a_history.logged_at if (a.last_a_history) else pytz.utc.localize(datetime(MINYEAR, 1, 1)), a.mac_address), reverse=reverse_flag)

Upvotes: -4

knbk
knbk

Reputation: 53649

You will have to annotate the maximum related date, filter for history objects where the max date equals the history's date, and order by a business_unit attribute. :

from django.db.models import Max, F

a = A.objects.annotate(last_date=Max('history_set__logged_at'))\
    .filter(history_set__logged_at=F('last_date'))\
    .order_by('history_set__business_unit__<attribute>')

This way, for each A object, you filter for the last History related object, and order on the associated business_unit. Using just history_set__business_unit will order by the business unit's id, so for any meaningful ordering you need to decide on which attribute of business_unit you want to order.

Upvotes: 8

Related Questions