How to annotate the number of M2M objects of a ManyToOneRelation field in a queryset?

Question

Django

I'm trying to sort a list of ticket objects that have chats inside by the number of unread messages in those chats. Reading the documentation on Django aggregation, I got completely confused.
There are models:

class Ticket(models.Model):
    name = models.CharField(max_length=100)
    user = models.ForignKey(AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="tickets")

class Chat(models.Model):
    name = models.CharField(max_length=100)
    participants = models.ManyToManyField(AUTH_USER_MODEL, related_name="chats")
    ticket = models.ForignKey("tickets.Ticket", on_delete=models.CASCADE)

class Message(models.Model):
    user = models.ForignKey(AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True)
    chat = models.ForignKey("tickets.Chat", on_delete=models.CASCADE)
    text = models.TextField()
    read = models.BooleanField(default=False)

I need to sort the list of user's tickets by the number of unread messages sent by NOT THIS USER in chats where participantsthis user exists in descending order.
How to do this without resorting to RawSQL?

Answer 1

The data model is incorrect. The message should become read for a specific chat participant, and for you - for everyone at once. And it's better to track the date of the first unread + the number in the "participant" model - then everything is much simpler.