CODEWITHSUNDEEP
pythondjangopostgresql
Dominix
Dominix

Reputation: 955

Django + postgres: orderby + distinct + filter() - strange behaviour

I have 2 simple models: Claim and ClaimStatus. Claims can have many statuses (eg. 1: Not analyzed yet, 2: Being analyzed, 3: Analyzed), all of which are written down in the database, together with claim_id and creation_date. The model of interest here - ClaimStatus - is created the following way:

class ClaimStatuss(models.Model):
    status =  models.IntegerField(choices=investigated_choices, default=1)
    claim_id = models.ForeignKey('Claim', to_field='claim_id')
    creation_date = models.DateTimeField(auto_now=True)

My aim is to select claims and their last (= active) statuses for filtering to show eg. all cases that are being analyzed at the moment, using the advice fron this thread: Django orm get latest for each group

TO make it simpler, I have just 2 objects in my database:

id: 6,claim_id: 578, status: 2, date: 2017-04-12 16:55:25.371014+00:00
id: 7,claim_id: 578, status: 3, date: 2017-04-12 17:04:06.944270+00:00

I do the following:

  1. Select all ClaimStatuss objects, and group them to have the latest one for each claim_id (only one in this example):
statuses = ClaimStatuss.objects.all().order_by('claim_id','-creation_date').distinct('claim_id')
  1. I check, if only one, the latest element has been selected for the queryset:
statuses.get().status
    3
  1. Now I filter this one-element Queryset, to leave only the objects, which contain status=2
statuses.filter(status=2).get().status
    2

And I see, that my Query containing just one object with status "3", after filtering found also the object with status "2"!

I presume that's the problem with the first command, but is this an expected behavior (if so, what am I doing wrong / what should I do to make it work) or some bug?

ADDITIONAL DETAILS:

Checking for the status=3 brings positive results too (All other statuses don't)

>>> statuses.filter(status=3).get().status
3

Weirdly enough, I can execute the get method on statuses variable, which just proved to contain 2 different objects:

>>> statuses.get().status
3

The ForeignKey is not the problem here - I tried changing it to String and the behavior is the same.

Upvotes: 0

Views: 92

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 599510

You are confused about the interaction between the distinct() call and the rest of the filter.

All distinct('claim_id) will do is ensure that one item per claim is returned. But you only ask for one item; when you add the filter for status=2, you still only get one item for the distinct value of claim_id. That it's not the same one you get if you don't add the filter is irrelevant.

Upvotes: 1

Related Questions