0

I have a profile model 

class Profile(models.model):
    user=models.Foreignkey(user,null=True,blank=True)

and

class Moods(models.model):
    email=models.CharField(null=True,blank=True)
    mood=models.CharField(null=True,blank=True)

for a single email there may be many mood like..

test@test.com happy
test@test.com sad

I want to get a queryset such that the email belongs to a user who has a foreign key in Profile model and the mood is the latest mood that is created for that user

Moses Koledoye
  • 77,341
  • 8
  • 133
  • 139
Chris Roberts
  • 99
  • 1
  • 6

1 Answers1

0

If your db backend is postgresql, you can use distinct(*fields):

Moods.objects\
    .filter(email__in=User.objects.filter(profile_set__isnull=False).values_list('email', flat=True))\
    .order_by('email', '-pk')\  # group by email, latest first
    .distinct('email')  # pick first for each email

The restrictions mentioned by @e4c5 with regard to the primary key not being a definitive indicator of record creation order still apply. Also note that email is not unique in auth.User, thus if users share the same email, only the latest mood of any of them will be in the queryset.

user2390182
  • 72,016
  • 6
  • 67
  • 89