CODEWITHSUNDEEP
djangodjango-orm
Mehran
Mehran

Reputation: 1304

Getting distinct objects of a queryset from a reverse relation in Django

class Customer(models.Model):
    name = models.CharField(max_length=189)

class Message(models.Model):
    message = models.TextField()
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name="messages")
    created_at = models.DateTimeField(auto_now_add=True)

What I want to do here is that I want to get the queryset of distinct Customers ordered by the Message.created_at. My database is mysql.

I have tried the following.

qs = Customers.objects.all().order_by("-messages__created_at").distinct()
m = Messages.objects.all().values("customer").distinct().order_by("-created_at")
m = Messages.objects.all().order_by("-created_at").values("customer").distinct()

In the end , I used a set to accomplish this, but I think I might be missing something. My current solution:

customers = set(Interaction.objects.all().values_list("customer").distinct())
customer_list = list()
for c in customers:
    customer_list.append(c[0])

EDIT Is it possible to get a list of customers ordered by according to their last message time but the queryset will also contain the last message value as another field?

Upvotes: 1

Views: 1043

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476659

Based on your comment you want to order the customers based on their latest message. We can do so by annotating the Customers and then sort on the annotation:

from dango.db.models import Max

Customer.objects.annotate(
    last_message=Max('messages__crated_at')
).order_by("-last_message")

A potential problem is what to do for Customers that have written no message at all. In that case the last_message attribute will be NULL (None) in Python. We can specify this with nulls_first or nulls_last in the .order_by of an F-expression. For example:

from dango.db.models import F, Max

Customer.objects.annotate(
    last_message=Max('messages__crated_at')
).order_by(F('last_message').desc(nulls_last=True))

A nice bonus is that the Customer objects of this queryset will have an extra attribute: the .last_message attribute will specify what the last time was when the user has written a message.

You can also decide to filter them out, for example with:

from dango.db.models import F, Max

Customer.objects.filter(
    messages__isnull=False,
).annotate(
    last_message=Max('messages__crated_at')
).order_by('-last_message')

Upvotes: 2

Related Questions