.get() has unexpected behaviour on a queryset after previously applying .order_by().distinct()

[Nick Smith]

>>> from django.db.models import models
>>> class MarketPrice(models.Model):
        market = models.CharField(max_length=30)
        crop = models.CharField(max_length=30)
        price = models.PositiveSmallIntegerField()
        date = models.DateField()

        def __str__(self):
            return "{}, {}, {}, {}".format(
                self.market,
                self.crop,
                self,price,
                self.date
            )

>>> FIXTURES = [
        ('London', 'carrots', 15, datetime.date(2015, 1, 1)),
        ('London', 'carrots', 20, datetime.date(2015, 1, 2)),

        ('London', 'potatoes', 12, datetime.date(2015, 1, 1)),
        ('London', 'potatoes', 14, datetime.date(2015, 1, 2)),

        ('Manchester', 'carrots', 18, datetime.date(2015, 1, 1)),
        ('Manchester', 'carrots', 21, datetime.date(2015, 1, 2)),

        ('Manchester', 'potatoes', 10, datetime.date(2015, 1, 1)),
        ('Manchester', 'potatoes', 12, datetime.date(2015, 1, 2)),
    ]

>>> for market, crop, price, date in FIXTURES:
        MarketPrice.objects.create(market=market,
                                   crop=crop,
                                   price=price,
                                   date=date)

We want to get only the latest prices for every possible combination of markets and crops...

>>> prices = (MarketPrice.objects
              .order_by('market', 'commodity', '-date')
              .distinct('market', 'commodity'))
[<MarketPrice: London, carrots, 20, 2015-01-02>, <MarketPrice: London, potatoes, 14, 2015-01-02>, <MarketPrice: Manchester, carrots, 21, 2015-01-02>, <MarketPrice: Manchester, potatoes, 12, 2015-01-02>]

.filter() works as expected

>>> prices.filter(market='Manchester', crop='carrots')
[<MarketPrice: Manchester, carrots, 21, 2015-01-02>]

but .get is totally unexpected

>>> prices.get(market='Manchester', crop='carrots')
<MarketPrice: Manchester, carrots, 18, 2015-01-01>

not only does it return the 'wrong' entry, but one which didn't even seem to be in prices (because it's a distinct queryset)

It looks and feels even weirder if you apply the filtering first:

>>> prices = (MarketPrice.objects
              .filter(market='Manchester', crop='carrots')
              .order_by('market', 'commodity', '-date')
              .distinct('market', 'commodity'))
>>> prices
[<MarketPrice: Manchester, carrots, 21, 2015-01-02>]

>>> prices.get(market='Manchester', crop='carrots')
<MarketPrice: Manchester, carrots, 18, 2015-01-01>

There's a note seemingly related to this at https://docs.djangoproject.com/en/1.7/ref/models/querysets/#django.db.models.query.QuerySet.distinct, but I'm not clear how it directly describes what I'm seeing here.

The line which seems to be causing this is from https://github.com/django/django/commit/e4757ec7afd54861e0c34d9b0f5edbbac4e2b860, which strips the ordering I've previously applied

>>> prices
[<MarketPrice: Manchester, carrots, 21, 2015-01-02>]

>>> prices.order_by()
[<MarketPrice: Manchester, carrots, 18, 2015-01-01>]

Is this intended behaviour?

PS the above code examples are untested, manually-typed pseudocode for the sake of keeping the example simple.

[Sergiy Khohlov]

Both methods use SQL request s.  Try to test using SQL directly. Database has no idea about a returning order. Good result for filter is "nice shot". Adding and deleting objects you broke filter method.
Use always order_by for this kind of purpose.

15 квіт. 2015 19:46, користувач "Nick Smith" <ni...@torchbox.com> написав:

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/50f5ba71-bb20-42f4-96bd-3f1ce17338bb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.