Django GROUP BY query

Question

Given a model with the following DB rows:

| Kind   | Age(days) |  Color |
-------------------------------
| Apple  |    1      | Red    |
| Apple  |    2      | Red    |
| Apple  |    3      | Red    |
| Apple  |    1      | Green  |
| Apple  |    2      | Green  |
| Plum   |    1      | Purple |
| Plum   |    2      | Purple |
| Cherry |    1      | Red    |
| Cherry |    2      | Red    |

I'd like to select the one oldest fruit of each color, so I should end up with:

| Kind   | Age(days) |  Color |
-------------------------------
| Apple  |    3      | Red    |
| Apple  |    2      | Green  |
| Plum   |    2      | Purple |

I know that in SQL it would look something like:

SELECT * FROM `fruit` GROUP BY `color` ORDER BY `age` DESC;

How is this done using the Django QuerySet API? Most of what I see regarding aggregate involves counting things, but I want the actual objects back, not a count of them.

You are looking for this: [Django aggregation](http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django) — PepperoniPizza, Mar 21 '13 at 23:19
@PepperoniPizza: I checked the question you referenced and went through the docs again, and still haven't found a way to use aggregation to do this. Care to elaborate? — visum, Mar 22 '13 at 22:38

score 0 · Accepted Answer · answered Mar 22 '13 at 02:25

0

def myview(request):    
    lists = []
    colors = Fruit.objects.values_list('color', flat=True).distinct()
    for color in colors:
        fruits = Fruit.objects.filter(color=color).order_by('-age')[:1]
        for fruit in fruits:
            lists.append({'kind': fruit.kind, 'age': fruit.age, 'color': fruit.color})
    return render(request, 'page.html', {'fruits': lists})

answered Mar 22 '13 at 02:25

catherine

22,492
12
61
85

It's not the solution I hoped for, but lacking a cleaner method this is basically what I ended up doing. – visum Mar 25 '13 at 15:13

Django GROUP BY query

1 Answers1