CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Evgeny Zislis
Evgeny Zislis

Reputation: 6957

Django self-referencing nested QuerySets

How can I represent this simple SQL using Django ORM?

SELECT * FROM alerts a1 WHERE timestamp = (
    SELECT MAX(timestamp) FROM alerts a2
        WHERE a1.category = a2.category
        AND   a1.type     = a2.type
)

The alerts table in this case is a history of all the possible alerts that happened, with a category tag on each - and the query returns the latest alerts for each category.

Upvotes: 1

Views: 818

Answers (1)

Ski
Ski

Reputation: 14487

It looks like there is no way to translate your query directly to django-orm. You will need to use raw query, or search for a different solution.

Here is one possible proposition:

  • I assume that pk is just like timestamp, the later created - the higher. So I group cols by category and type and then select MAX('pk') instead of MAX('timestamp').

    latest_events_pks = Event.objects.values('category', 'typ') \
                                 .annotate(max=Max('pk')) \
                                 .values_list('max', flat=True)

    It generates a query like this:

    SELECT MAX("event"."id") AS "max" FROM "event" 
GROUP BY "event"."category", "event"."typ"

  • values_list() returned a list of id for example: [1, 15, 20, 22]. So it is possible to select appropriate events:

    Event.objects.filter(id__in=latest_events_pks)

It is 2 queries but they should be more efficient than a query with sub query.

Upvotes: 1

Related Questions