Simple Django filter query: group by primary key?

Question

I am running a filter query in Django with range. I thought filter always returned a queryset that was grouped by the primary key, but it seems not.

These are my models:

class MCode(models.Model):
    code = models.CharField(max_length=16)
class Product(models.Model):
    id = models.CharField(max_length=40, primary_key=True, db_index=True)
    mcode = models.ForeignKey(MCode, null=True, blank=True, db_index=True)
class Review(models.Model):
    review_id = models.CharField(max_length=32, primary_key=True, db_index=True) 
    product = models.ForeignKey(Product, db_index=True) 
    rating = models.IntegerField() 
    time = models.DateTimeField(db_index=True)

And this is my code:

mcode = 'M83'
base = Product.objects
tcode_obj = MCode.objects.filter(code=mcode.upper())
return base.filter(tcode=tcode_obj, 
                   review__time__range=[date_from, date_to])

I'm getting five results, but three of them have the same primary key. It looks like I'm getting a result for each review, not each product.

Does anyone know how could I group these products by ID, and annotate with the count of the attached reviews?

When you say "group" do you mean "sort"? "Group by" is a totally different concept. — Chris Pratt, Jul 23 '12 at 16:09
I just want a result for each product, not for each review. Maybe I should do this on the client side, but I was wondering if it was possible with Django. — flossfan, Jul 23 '12 at 16:10
That does the trick! Thank you. If you want to submit it as an answer, I'll accept it - you may think it's too basic to be an answer, but it might help other newbies searching.... — flossfan, Jul 23 '12 at 17:10

score 1 · Accepted Answer · answered Jul 23 '12 at 17:14

When you select based on something that could match multiple times, such as Product with Reviews during a range of time, each match is added to the queryset, even if it's a duplicate of an item already there. There's actually times where you might want this behavior, but if you need to limit the queryset to just unique items, use .distinct() at the end of your query. Getting duplicates is also common when using logical ORs in your query, so be aware of times you do that and remember to use .distinct() there as well.

Simple Django filter query: group by primary key?

1 Answers1