Getting distinct rows based on a certain field from a database in Django

Question

I need to construct a query in Django, and I'm wondering if this is somehow possible (it may be really obvious but I'm missing it...).

I have a normal query Model.objects.filter(x=True)[:5] which can return results like this:

FirstName    LastName    Country
Bob           Jones        UK
Bill          Thompson     UK
David         Smith        USA

I need to only grab rows which are distinct based on the Country field, something like Model.objects.filter(x=True).distinct('Country')[:5] would be ideal but that's not possible with Django.

The rows I want the query to grab ultimately are:

FirstName    LastName    Country
Bob           Jones        UK
David         Smith        USA

I also need the query to use the same ordering as set in the model's Meta class (ie. I can't override the ordering in any way).

How would I go about doing this?

Thanks a lot.

This is a tough one but I'm not sure that I'm fully understanding the question. Do you want the top five rows in which the country is distinct? — jathanism
– jathanism, Commented Nov 23, 2009 at 17:53
@S.Lott, slicing a queryset does not turn it into a list. Dx143 is correct that this is the way to just get the top 5 elements from the database. — Daniel Roseman
– Daniel Roseman, Commented Nov 23, 2009 at 19:36
Looks like there's a ticket for this: code.djangoproject.com/ticket/6422 — shacker
– shacker, Commented Feb 4, 2010 at 20:38

extraneon · Accepted Answer · 2009-11-23 21:16:48Z

4

I haven't tested this, but it seems to me a dict should do the job, although ordering could be off then:

d = {}
for x in Model.objects.all():
    d[x.country] = x

records_with_distinct_countries = d.values()

edited Nov 23, 2009 at 21:16

answered Nov 23, 2009 at 19:03

extraneon

24.1k2 gold badges51 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jathanism Over a year ago

This same thing can be performed with capturing the values in a set.

extraneon Over a year ago

Not really. The countries can be put in the set as they are immutable. But the models are definitely not immutable, so can't be in a set. That's why I use a dict, the immutable country as key, and the record as value. I happen to use the dict keys as a set (which it conceptually is) without losing the record.

skrobul · Accepted Answer · 2009-11-23 17:47:01Z

0

countries = [f.country in Model.objects.all()]

for c in countries:
    try:
        print Model.objects.filter(country=c)
    except Model.DoesNotExist:
        pass

answered Nov 23, 2009 at 17:47

skrobul

1821 silver badge5 bronze badges

Comments

jathanism · Accepted Answer · 2009-11-23 18:50:20Z

0

I think that @skrobul is on the right track, but a little bit off.

I don't think you'll be able to do this with a single query, because the distinct() method adds the SELECT DISTINCT modifier to the query, which acts on the entire row. You'll likely have to create a list of countries and then return limited QuerySets based on iterating that list.

Something like this:

maxrows = 5
countries = set([x.country for x in Model.objects.all()])
rows = []
count = 0
for c in countries:
    if count >= maxrows:
        break

    try:
        rows.append(Model.objects.filter(country=c)[0])
    except Model.DoesNotExist:
        pass

    count += 1

This is a very generic example, but it gives the intended result.

answered Nov 23, 2009 at 18:50

jathanism

33.9k9 gold badges72 silver badges89 bronze badges

Comments

morrissimo · Accepted Answer · 2009-11-23 19:14:16Z

0

Can you post the raw SQL that returns what you want from the source database? I have a hunch that the actual problem here is the query/data structure...

answered Nov 23, 2009 at 19:14

morrissimo

215 bronze badges

Collectives™ on Stack Overflow

Getting distinct rows based on a certain field from a database in Django

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related