model

class UserAction(models.Model):
    ACTION_CHOICES = (
        ("LogIn", "Entered"),
        ("LogOut", "Gone Out"),
        ("Away", "Away from Computer"),
        ("Busy", "Busy"),
        ("DoNotDisturb", "Do not disturb"),
        ("Online", "Online and Available"),
    )
    action_name = models.CharField(choices=ACTION_CHOICES, default="LogIn")
    user = models.ForeignKey(Profile, related_name="actions")
    action_time = models.DateTimeField(default=timezone.now(), editable=False)


class Profile(models.Model):
    name = models.CharField(max_length=120)
    is_active = models.BooleanField(default=True)
    date_joined = models.DateTimeField(default=timezone.now())

I need to query UserAction, in such a way that I want only the last UserAction of each user. My solutions were too much time consuming. That's why looking for an optimised answer.

CodePudding user response：

You can annotate the Profiles with a Subquery expression [Django-doc]:

from django.db.models import OuterRef, Subquery

Profile.objects.annotate(
    last_action=Subquery(
        UserAction.objects.filter(
            user_id=OuterRef('pk')
        ).order_by('-action_time').values('action_name')[:1]
    )
)

The Profiles in the queryset will have an extra attribute .last_action with the action_name of the last related UserAction.

Note: Django's DateTimeField [Django-doc] has a auto_now_add=… parameter [Django-doc] to work with timestamps. This will automatically assign the current datetime when creating the object, and mark it as non-editable (editable=False), such that it does not appear in ModelForms by default.