How can I represent this simple SQL using Django ORM?
SELECT * FROM alerts a1 WHERE timestamp = (
SELECT MAX(tim开发者_开发技巧estamp) 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.
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 bycategory
andtype
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.
精彩评论