How to translate query from SQL to Django ORM?

Question

Django

Help with converting SQL query to Django ORM:

count_limit = 10
content_type = ContentType.objects.get_for_model(Task)

last_tests = Task.objects.raw("\
SELECT task.id, task.lesson_id, task.name, task.content, task .max_score, MAX (log.action_time) AS modified_at\
FROM main_task task, django_admin_log log\
WHERE task.is_active=True AND log.content_type_id=%d AND task.id=CAST(log.object_id AS INTEGER)\
GROUP BY task. id, log.object_id ORDER BY modified_at DESC LIMIT %d\
" % (content_type.id, count_limit)
)

I pull out the events associated with the table (Task) from the LogEntry and by task.id show the last event in the LogEntry - who last modified the entry through the admin panel, and if the first event is the creator of the entry.
The LogEntry table is standard.
Thanks

Answer 1

ok, I decided to figure it out.
Built this ORM:
count_limit = 10
content_type = ContentType.objects.get_for_model(Task)
last_tests = Task.objects.filter(is_active=True).annotate(modified_at=Subquery(
LogEntry.objects.filter(content_type_id=content_type.id, object_id= str(OuterRef('pk')),).order_by(
'-action_time').values('action_time')[:1]
)).order_by('-modified_at')[:count_limit]
Here is his SQL:
SELECT " main_task"."id", "main_task"."lesson_id", "main_task"."name", "main_task"."max_score", "main_task"."content", "main_task"."is_active",
(SELECT U0 .
WHERE (U0."content_type_id" = 11 AND U0."object_id" = OuterRef(pk) )
ORDER BY U0."action_time" DESC LIMIT 1) AS "modified_at"
FROM "main_task"
WHERE "main_task"."is_active" = True
ORDER BY "modified_at" DESC LIMIT 10
As a result, the modified_at field is empty. Does the OuterRef construct work at all?