How to get distinct list of names from a consultation list, and show only latest consultation in Django?

What I want to accomplish is to get a unique list of the names of customers with their lastest consultation date.

I have defined these models in my models.py file, using mySQL as my database:

class Customer(models.Model):
  class ContactChoice(models.IntegerChoices):
    DO_NOT_CONTACT = 0
    EMAIL = 1
    TXT_SMS_VIBER = 2

  mobile_num = models.CharField('Mobile Number', max_length=10, unique=True,)
  email_add = models.EmailField('Email', max_length=150, unique=True,)
  last_name = models.CharField('Last Name', max_length=30,)
  first_name = models.CharField('First Name', max_length=30,)
  contact_for = models.CharField('Contact For', max_length=60,)
  contact_on = models.IntegerField('Contact Using', choices=ContactChoice.choices, default=0,)
  customer_consent = models.BooleanField('With Consent?', default=False,)

  def __str__(self):
    return self.last_name + ', ' + self.first_name

class Consultation(models.Model):
  consultation_date = models.DateTimeField('Date of Consultation', default=now)
  customer = models.ForeignKey(Customer, on_delete=models.SET_DEFAULT, default=1)
  concern = models.ForeignKey(SkinConcern, on_delete=model开发者_如何学编程s.SET_DEFAULT, default=1)
  consultation_concern = models.CharField('Other Concerns', max_length=120, null=True,)
  product = models.ForeignKey(Product, on_delete=models.SET_DEFAULT, default=1)
  user = models.ForeignKey(User, on_delete=models.SET_DEFAULT, default=1)
  store = models.ForeignKey(Store, on_delete=models.SET_DEFAULT, default=1)
  consultation_is_active = models.BooleanField('Is Active', default=True)

  def __str__(self):
    return self.customer.last_name + ", " + self.customer.first_name

In my views.py, I have this for the Consultations page:

distinct = Consultation.objects.values('customer').annotate(consultation_count=Count('customer')).filter(consultation_count=1)
consults = Consultation.objects.filter(customer__in=[item['customer'] for item in distinct])

As mentioned, I was expecting to get a unique list of customer names with their latest consultation dates. This code results in only 1 record being shown.

Can you point me in the right direction for this? Thank you in advance! :)

As I see it, what you're doing right now is gathering all the customers that only had one consultation. This won't return what you want.

I believe you can use the latest() method for this: https://docs.djangoproject.com/en/4.1/ref/models/querysets/#latest

This is untested code, but you could do something like this:

# gather all the customers
customers = Customer.objects.all()

# for every customer, get their latest consultation date, using the .latest() function
for customer in customers:
    try:
        latest_consultation = Consultation.objects.filter(customer=customer).latest('consultation_date')
        latest_consultation_date = latest_consultation.consultation_date
    except Consultation.DoesNotExist:
        latest_consultation_date = None

    customer.latest_consultation_date = latest_consultation_date

you can then loop over it as so:

for customer in customers:
    if customer.latest_consultation_date:
        print(customer.latest_consultation_date)