Django: query filter-CodePudding

I have two models that are related: one is a list of participants. The other is a list of times they have checked in or out of an office.

The table (Checkin) has one record for every checkin/checkout pair. So, there can be many records for any participant.

How can I retrieve only the very last (most recent) record for a participants checkin and then pass the participant and only that most recent Checkin record to my template?

From what I can tell there's no ability to do something like a last() in my template, so how would I go about filtering to get just that single record?

Thank you.

Models:

class Participant(models.Model):

    first_name = models.CharField(max_length=50)
    middle_initial = models.CharField(max_length=50, blank=True)


class CheckIn(models.Model):
    adult = models.ForeignKey(
        Participant, on_delete=models.CASCADE, blank=True, null=True, related_name='adult_checkin')

    checkin = models.DateTimeField(blank=True, null=True)
    checkout = models.DateTimeField(blank=True, null=True)

View snipit:

    p_checkins = Participant.objects.all().order_by('created')

    queryset = p_checkins

    context_object_name = "the_list"

    template_name = 'list_of_checkins.html'

CodePudding user response：

You can fetch data through most recent checkin or checkout.

For checkin :

p_checkins = CheckIn.objects.all().order_by('-checkin')[0]

For checkout :

p_checkins = CheckIn.objects.all().order_by('-checkout')[0]

To get the participant name by :

name = p_checkins.adult.first_name

CodePudding user response：

When you use (-) your latest update will be query from database.

p_checkins = CheckIn.objects.all().order_by('-checkin')

p_checkins = CheckIn.objects.all().order_by('-checkout')

CodePudding user response：

you can annotate the latest value via a subquery to the participant

from django.db.models import OuterRef, Subquery
checkin_q = CheckIn.objects.filter(adult=OuterRef('pk')).order_by('-checkin')
queryset = Participant.objects.annotate(last_checkin=Subquery(checkin_q.values('checkin')[:1]))

see https://docs.djangoproject.com/en/4.0/ref/models/expressions/#subquery-expressions

CodePudding user response：

Most of the answers so far are correct in several aspects. One thing to note is that if your check_in or check_out values (whichever you use) isn't chronological (and by "most recent", you mean the last added), you'll want to add a created_at datetime field with auto_now option True, or order by the pk.

In addition to the other answers provided and my comment above, you can also get the most recent check in by using the related manager on the participant object.