how to filter fk related objects on django-CodePudding

I want to query the books that an specific person has lend currently. I use this query Book.objects.filter(lends__person__username__iexact="Antony") but it's not working properly.

what i want to get is:

the books with their latest person username who lend. (not the persons whom lend before the latest)

here is my models

class Person(models.Model):
    username = models.CharField(max_length=32, unique=True)


class Book(models.Model):
    name = models.CharField(max_length=50)
    author = models.CharField(max_length=50)


class Lends(models.Model):
    book = models.ForeignKey(
        "Book", on_delete=models.CASCADE, related_name="lends"
    )
    person = models.ForeignKey(
        "Person", on_delete=models.CASCADE, related_name="lends"
    )

CodePudding user response：

what i want to get is:
the books with their latest person username who lend. (not the persons whom lend before the latest)

it can be done by using Subquery

from django.db.models import OuterRef, Subquery

latest_lended_person = Lend.objects.filter(
    book=OuterRef('pk')
).order_by('-pk')

books = Book.objects.annotate(
    latest_lended_person_username=Subquery(
        latest_lended_person.values('username')[:1]
    )
)

CodePudding user response：

You are looking for temporal information. I recommend you add a field called created_at to the Lends class. This attribute will be populated automatically every time an object is created. By doing it you can sort in descending order.

class Lends(models.Model):
    book = models.ForeignKey(
        "Book", on_delete=models.CASCADE, related_name="lends"
    )
    person = models.ForeignKey(
        "Person", on_delete=models.CASCADE, related_name="lends"
    )
    created_at = models.DateTimeField(auto_now_add=True)

So, right now you can filter and order the queryset.

last_lend = Lends.objects.filter(lends__person__username__iexact="Antony").order_by("-created_at").first()

You can see how ordering works here: https://docs.djangoproject.com/en/4.1/ref/models/options/#:~:text=initial migrate.-,ordering,-¶