How to execute an order_by after using distinct?

Question

I would like to sort my result after I have grabbed unique results based on two fields. The problem I'm having is that I cannot use two order_by in Django. Django clears the previous order_by() whenever you use this function.

Each order_by() call will clear any previous ordering.

Example of models:

class Product(models.Model):
    price = models.FloatField()
    name = models.CharField(max_length=100)
    group = models.CharField(max_length=100)

I want to get the product which is in the same group and have the lowest. Then, I'd like to sort them by price. Something like this:

Product.objects.order_by('group','price').distinct('group').order_by('price')
Product.objects.order_by('group','price').distinct('group').order_by('-price')

The thing is that if just use in the first order by I get different products.

--

EDIT

Example of table:

id | name | group | price
0  | aaa  | aaa   | 10.0
1  | aaa  | aaa   | 1.0
2  | aaa  | aaa   | 2.0
3  | aaa  | aaa   | 1.0
4  | bbb  | bbb   | 2.0
5  | bbb  | bbb   | 2.1
6  | bbb  | bbb   | 10.0

Sorting by price:

1  | aaa  | aaa  | 1.0
4  | bbb  | bbb  | 2.0

Sorting by -price:

4  | bbb  | bbb  | 2.0
1  | aaa  | aaa  | 1.0

The problem I have is that when I sort using order_by the products returned are different.

In sql statement would be something like this:

SELECT * 
FROM product
WHERE id IN (
  SELECT DISTINCT ON 
  (group) id
  FROM product
  ORDER BY group ASC, price ASC
)
ORDER BY price DESC

I'm using PostgreSQL

Answer 1

Ok, here is what I did:

products = Product.objects.order_by('group','price').distinct('group')
result = Product.objects.filter(id__in=products).order_by('-price')

I don't think it's the most efficient way, but it is working...