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.

Answer 1

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

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

Answer 2

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.

Answer 3

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()

Answer 4

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...