CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-queryset
n.imp
n.imp

Reputation: 807

Filter data using max value

I have a model consists of order field , I want to fetch that row who have max value for order.

Class MyModel(models.Model):
   user = models.ForeignKey(User)
   order = models.IntegerField()

Now let say I want to fetch records for user 1 , for this user total no of rows exist in table are 10 but I want only that row who have max order value.

Also If I wanted to fetch records for two users then same condition must be applied. For this filter query must return one row for every user with max value for order col.

right I am using annotate but it returns all rows 

rows = MyModel.objects.annotate(max_seq=Max('order')).filter(user = 1)

so when I loop over this rows it print all 10 rows , but it must return only one row with col max order value

Upvotes: 1

Views: 1263

Answers (3)

Paolo Melchiorre
Paolo Melchiorre

Reputation: 6112

I solved your problem using only Django ORM.

Example data:

MyModel.objects.values('user__id', 'order')
[{'user__id': 1, 'order': 1}, {'user__id': 1, 'order': 2}, {'user__id': 2, 'order': 1}, {'user__id': 2, 'order': 2}, {'user__id': 2, 'order': 3}]

and the query:

MyModel.objects.values("user").annotate(ordermax=Max("order")).order_by()
[{'ordermax': 2, 'user': 1}, {'ordermax': 3, 'user': 2}]

Django doc reference: Interaction with default ordering or order_by()

Upvotes: 0

stellasia
stellasia

Reputation: 5612

Try using order_by and take only the first returned element : 

rows = MyModel.objects.filter(user = 1).order_by("orders")[0]

Following comment, this solution will not work to get the max for all users. I would do it the other way around, starting from the User model :

users = User.objects.annotate(Max("mymodel__order"))
print users[0].mymodel__order__max

see also this part of the django doc

Upvotes: 1

Zealous System
Zealous System

Reputation: 2324

You can put users (which you wanted to filter records of) in list and then iterate through them and fire a query what stellasia suggested..

users = [1,2,3]

for user in users:
   row = MyModel.objects.filter(user__id = user).order_by("order")[0]

Here it will give the result you wanted even if there is only one user in users list..

Upvotes: 2

Related Questions