Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Python

Natalie Tan
Natalie Tan
25,519 Points
Posted by Natalie Tan
Natalie Tan
25,519 Points

DJANGO ORM - How to Get MAX for a GROUPBY, but return other fields for the MAX row as well?

HI, I've been doing the Django courses, and starting my own project with a sqlite db.

I have a model:

class C_Ingredient(models.Model):
    category = models.ForeignKey('Ingredient', on_delete=models.CASCADE)
    product = models.CharField(max_length=250)
    packageprice = models.CharField(max_length=250, null=True)
    datescraped = models.DateField(null=True, blank=True)

Category is like Apple, Banana, Pear Product is like Fuji Apple, Green Apple, Red Apple

For each product, i want to get the most recent data

I can write a query which returns the most recent date per product, but I can't find how to include the most recent price for that product in the same query.

products = models.Coles_Ingredient.objects.filter(category=pk).order_by('product').values('product').annotate(latest=Max('datescraped'))

Can anybody help me?

[MOD: added ```python formatting -cf]

Natalie Tan
Natalie Tan
25,519 Points
Natalie Tan
Natalie Tan
25,519 Points

Using raw SQL: products = models.Coles_Ingredient.objects.raw(''' SELECT p1.id, p1.category_id, p1.product, p1.packageprice, p1.datescraped FROM ingredients_coles_ingredient AS p1, ( SELECT product, MAX(datescraped) AS max_datescraped FROM ingredients_coles_ingredient GROUP BY product ) AS p2 WHERE p1.product = p2.product AND p1.datescraped = p2.max_datescraped AND p1.category_id = %s ORDER BY p1.product ''', [pk])

The problem with using raw though, is that i then can't use queryset filters like count in the template.

Kenneth Love