How to order by queryset from another model field?

Question

class Item(models.Model):
    name = models.CharField(max_length=255)
    user = models.ForeignKey(User)

class Document(models.Model):
   doc_type = models.CharField(max_length=10, default="DOC")
   item = models.ForeignKey(Item, related_name="docs")
   uploaded_at = models.DateTimeField(auto_now_add=True)


@api_view(["GET"])
def get_items(request):
    # docs__uploaded_at should be from objects having doc_type="DOC" only
    # doc = Document.objects.filter(item=item, doc_type="DOC")
    items = Item.objects.prefetch_related("docs").filter(user=request.user).order_by("docs__uploaded_at")

Here I want to order items queryset based on document uploaded_at field having doc_type="DOC" only.

Answer 1

You could try like the following.

items = Item.objects.prefetch_related("docs").filter(user=request.user).filter(docs__doc_type = "DOC").order_by("docs__uploaded_at")

Answer 2

You can determine the uploaded_at of the latest item with 'DOC' as doc_type:

from django.db.models import F, Max, Q
from django.db.models.functions import Coalesce


@api_view(['GET'])
def get_items(request):
    items = Item.objects.filter(user=request.user).alias(
        latest_upload=Coalesce(
            Max('docs__uploaded_at', filter=Q(docs__doc_type='DOC')),
            F('created_at')
        )
    ).order_by('-latest_upload')
    # …

Here we thus determine the maximum (latest) of the docs__uploaded_at for docs with doc_type='DOC', and in case there is no item (then Max(…) is NULL), we fallback on the created_at field.

---

Note: django-1.9 is no longer supported ^[Django-doc] since April 2017, you should consider upgrading the Django version.