CODEWITHSUNDEEP
pythondjangodjango-queryset
doniyor
doniyor

Reputation: 37914

django - annotate() instead of distinct()

I am stuck in this issue:

I have two models:

Location and Rate.

each location has its rate, possibly multiple rates.

i want to get locations ordered by its rates, ascendingly.

obvouisly, order_by and distinct() dont work together: 

locations = Location.objects.filter(**s_kwargs).order_by('locations_rate__rate').distinct('id')

then i read the docs and came to annotate(). but i am not sure whether i have to use a function between annotate.

if i do this: 

locations = Location.objects.filter(**s_kwargs).annotate(rate=Count('locations_rate__rate')).order_by('rate')

but this counts the rates and orders by the sum. i want to get locations with its rates ordered by the value of those rates.

my model definitions are: 

class Location(models.Model):
  name = models.TextField()
  adres = models.TextField()

class Rate(models.Model):
  location = models.ForeignKey(Location,related_name='locations_rate')
  rate = models.IntegerField(max_length=2)
  price_rate = models.IntegerField(max_length=2) #<--- added now
  datum = models.DateTimeField(auto_now_add=True,blank=True) #<--- added now

Upvotes: 0

Views: 771

Answers (4)

drabo2005
drabo2005

Reputation: 1096

annotate(*args, **kwargs),Annotates each object in the QuerySet with the provided list of aggregate values (averages, sums, etc) that have been computed over the objects that are related to the objects in the QuerySet.

So if you want only to get locations ordered by its rates, ascendingly you dont have to use annotate()

     you can try this :

     loc = Location.objects.all()
     rate = Rate.objects.filter(loc=rate__location).order_by('-rate')

Upvotes: 1

Hieu Nguyen
Hieu Nguyen

Reputation: 8623

Possibly you want something like this:

locations = (Location.objects.filter(**s_kwargs)
             .values('locations_rate__rate')
             .annotate(Count('locations_rate__rate'))
             .order_by('locations_rate__rate'))

You need the Count() since you actually need a GROUP BY query, and GROUP BY only works with aggregate functions like COUNT or SUM.

Anyway I think your problem can be solved with normal distinct():

locations = (Location.objects.filter(**s_kwargs)
             .order_by('locations_rate__rate')
             .distinct('locations_rate__rate'))

Why would you want to use annotate() instead?

I haven't tested both but hope it helps.

Upvotes: 1

Debanshu Kundu
Debanshu Kundu

Reputation: 805

Well the issue is not how to make query in Django for the problem you described. It's that your problem is either incorrect or not property thought through. Let me explained with an example:

Suppose you have two Location objects, l1 and l2. l1 has two Rate objects related to it, r1 and r3, such that r1.rate = 1 and r3.rate = 3; And l2 has one rate object related to it, r2, such that r2.rate = 2. Now what should be the order of your query's result l1 followed l2 or l2 followed by l1?? As one of l1's rate is less than l2's rate and the other one is greater than l2's rate.

Upvotes: 1

Aamir Rind
Aamir Rind

Reputation: 39689

Try this:

from django.db.models import Count, Sum
# if you want to annotate by count of rates
locations = Location.objects.filter(**s_kwargs) \
    .annotate(rate_count = Count('locations_rate')) \
    .order_by('rate_count')

# if you want to annotate on values of rate e.g. Sum
locations = Location.objects.filter(**s_kwargs) \
    .annotate(rate_count = Sum('locations_rate')) \
    .order_by('rate_count')

Upvotes: 1

Related Questions