CODEWITHSUNDEEP
djangodjango-models
David Downes
David Downes

Reputation: 1205

How can I filter a Django queryset by the latest of a related model?

Imagine I have the following 2 models in a contrived example:

class User(models.Model):
    name = models.CharField()

class Login(models.Model):
    user = models.ForeignKey(User, related_name='logins')
    success = models.BooleanField()
    datetime = models.DateTimeField()

    class Meta:
        get_latest_by = 'datetime'

How can I get a queryset of Users, which only contains users whose last login was not successful.

I know the following does not work, but it illustrates what I want to get:

User.objects.filter(login__latest__success=False)

I'm guessing I can do it with Q objects, and/or Case When, and/or some other form of annotation and filtering, but I can't suss it out.

Upvotes: 1

Views: 87

Answers (3)

Jonatan
Jonatan

Reputation: 1242

You can first annotate the max dates, and then filter based on success and the max date using F expressions:

User.objects.annotate(max_date=Max('logins__datetime'))\
    .filter(logins__datetime=F('max_date'), logins__success=False)

Upvotes: 0

user11512334
user11512334

Reputation:

for check bool use success=False and for get latest use latest() your filter has been look this:

User.objects.filter(success=False).latest()

Upvotes: -1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

We can use a Subquery here:

from django.db.models import OuterRef, Subquery

latest_login = Subquery(Login.objects.filter(
    user=OuterRef('pk')
).order_by('-datetime').values('success')[:1])

User.objects.annotate(
    latest_login=latest_login
).filter(latest_login=False)

This will generate a query that looks like:

SELECT auth_user.*, (
    SELECT U0.success
    FROM login U0
    WHERE U0.user_id = auth_user.id
    ORDER BY U0.datetime DESC
    LIMIT 1
  ) AS latest_login
FROM auth_user
WHERE (
    SELECT U0.success
    FROM login U0
    WHERE U0.user_id = auth_user.id
    ORDER BY U0.datetime
    DESC LIMIT 1
  ) = False

So the outcome of the Subquery is the success of the latest Login object, and if that is False, we add the related User to the QuerySet.

Upvotes: 2

Related Questions