Django Inner Join on Derived Query

Question

I have two models shown as follows. I want to be able to execute this query through the django ORM, essentially giving me the CustomUser class alongside two derived fields: max(message.sent_at) and max(case when read_at is null then 1 else 0 end). Those two fields would enable me to sort threads of messages by usernames and latest activity.

Here are my classes:

class CustomUser(AbstractBaseUser, PermissionsMixin):
    username_validator = UnicodeUsernameValidator()

    username = models.CharField(_('username'), max_length=150, unique=True, help_text=_('Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only.'), validators=[username_validator], error_messages={'unique': _('A user with that username already exists.'),},)
    email = models.EmailField(_('email address'), blank=True)
    first_name = models.CharField(_('first name'), max_length=30, blank=True)
    last_name = models.CharField(_('last name'), max_length=150, blank=True)
    is_staff = models.BooleanField(_('staff status'), default=False, help_text=_('Designates whether the user can log into this admin site.'),)
    is_active = models.BooleanField(_('active'), default=True, help_text=_('Designates whether this user should be treated as active. Unselect this instead of deleting accounts.'),)
    date_joined = models.DateTimeField(_('date joined'), default=timezone.now)
    bio = models.TextField(max_length=500, null=True, blank=True)
    location = models.CharField(max_length=30, null=True, blank=True)
    birth_date = models.DateField(null=True, blank=True)
    phone_number = PhoneNumberField(default='+10000000000')
    gender = models.CharField(max_length=32, choices=[(tag.name, tag.value) for tag in GenderChoice], default=GenderChoice.UNSPECIFIED.value)

    objects = UserManager()
    EMAIL_FIELD = 'email'
    USERNAME_FIELD = 'username'
    REQUIRED_FIELDS = ['email']
    class Meta:
        ordering = ['username']
        verbose_name = _('user')
        verbose_name_plural = _('users')

and

class Message(AbstractIP):
    subject = models.CharField(_('Subject'), max_length=120, blank=True)
    body = models.TextField(_('Body')) # Do we want to cap length or enforce non-blank?
    sender = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='sender_messages', verbose_name=_('Sender'), on_delete=models.CASCADE)
    recipient = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='receiver_messages', verbose_name=_('Recipient'), blank=True, on_delete=models.CASCADE)
    parent_msg = models.ForeignKey('self', related_name='next_messages', null=True, blank=True, verbose_name=_('Parent message'), on_delete=models.CASCADE)
    sent_at = models.DateTimeField(_('sent at'), null=True, blank=True)
    read_at = models.DateTimeField(_('read at'), null=True, blank=True)
    replied_at = models.DateTimeField(_('replied at'), null=True, blank=True)
    sender_deleted_at = models.DateTimeField(_('Sender deleted at'), null=True, blank=True)
    recipient_deleted_at = models.DateTimeField(_('Recipient deleted at'), null=True, blank=True)
    ip = models.GenericIPAddressField(verbose_name=_('IP'), null=True, blank=True)
    user_agent = models.CharField(verbose_name=_('User Agent'), blank=True, max_length=255)

    objects = MessageManager() # Manager for Message queries

    def new(self):
        """Returns whether the recipient has read the message or not"""
        if self.read_at is not None:
            return False
        return True

    def replied(self):
        """Returns whether the recipient has written a reply to this message"""
        if self.replied_at is not None:
            return True
        return False

    def __str__(self):
        if self.subject is not None:
            return self.subject
        if self.body is not None:
            return self.body[:40]
        return None

    def get_absolute_url(self):
        return reverse('messages_detail', args=[self.id])

    def save(self, **kwargs):
        if not self.id:
            self.sent_at = timezone.now()
        super(Message, self).save(**kwargs)

    class Meta:
        ordering = ['-sent_at']
        verbose_name = _('Message')
        verbose_name_plural = _('Messages')

The query I want to be able to perform equates to this, but I cannot figure out how to do it in the ORM, where %s is a placeholder for the CustomUser.id (pk) field of a given user.

SELECT webrtc_customuser.*
      ,MAX(webrtc_message.sent_at) AS sent_at
      ,MAX(CASE WHEN webrtc_message.read_at IS NULL AND webrtc_customuser.id <> webrtc_message.sender_id THEN 1 ELSE 0 END) AS has_unread
FROM webrtc_customuser
INNER JOIN webrtc_message
  ON (
    webrtc_customuser.id = webrtc_message.sender_id
    AND webrtc_message.sender_id = %s
    AND webrtc_message.sender_deleted_at IS NULL
  ) OR (
    webrtc_customuser.id = webrtc_message.recipient_id
    AND webrtc_message.recipient_id = %s
    AND webrtc_message.recipient_deleted_at IS NULL
  )

I managed to get the correct user_id and derived fields with the following queries but cannot figure out how to get the CustomUser properties joined alongside them.

messages = self.values(
    user_fk=Case(When(sender=user, then='recipient'), default='sender', output_field=models.IntegerField())
).exclude(
    sender=user, recipient=user
).filter(
    Q(sender=user, sender_deleted_at__isnull=True) |
    Q(recipient=user, recipient_deleted_at__isnull=True)
).annotate(
    max_sent_at=Max('sent_at'),
    has_unread=Max(Case(When(~Q(sender=user) & Q(read_at__isnull=True), then=1), default=0, output_field=models.IntegerField()))
).order_by()

Thank you in advance for your time!

Edit: updated ORM query

Answer 1

You need to specify the desired user properties individually:

messages = self.values(
    user_email=Case(When(sender=user, then='recipient__email'), default='sender__email'),
    user_username=Case(When(sender=user, then='recipient__username'), default='sender__username'),
)

Not very pretty, particularly as you have to repeat the CASE statement for every column and may even need to specify an output_field for every one.

To get around that, ie. to get all user properties without selecting them one by one, you'd either need to a) select from CustomUser.object (figuring out how to select the relevant users and get the relevant annotations), or b) select full message objects rather than just a values() dictionary. Then you can access the full user objects via message.senderand message.recipient. But here again, the challenge would be how to filter and annotate the messages queryset using subqueries, since just omitting values() will bust the aggregates in your annotations as every message object will then be unique.