CODEWITHSUNDEEP
pythondjangodjango-modelssqlitedjango-forms
person10559
person10559

Reputation: 57

Queryset distinct() with filter() and order_by() does not work

I am using the following models and forms to build two dropdown menus. The City menu is dependent upon the Country menu. Right now my code does the filtering and ordering correctly. However, distinct() is not working. I've played around with the ordering of the queries for a while and it still doesn't work. How can I fix this?

models.py

from django.db import models

class Country(models.Model):
    name = models.CharField(max_length=30)

    def __str__(self):
        return self.name

class City(models.Model):
    country = models.ForeignKey(Country, on_delete=models.CASCADE)
    name = models.CharField(max_length=30)

    def __str__(self):
        return self.name

class Person(models.Model):
    country = models.ForeignKey(Country, on_delete=models.SET_NULL, null=True)
    city = models.ForeignKey(City, on_delete=models.SET_NULL, null=True)

    def __str__(self):
        return self.name

forms.py

from django import forms
from .models import Person, City

class PersonForm(forms.ModelForm):
    class Meta:
        model = Person
        fields = ('country', 'city')

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.fields['city'].queryset = City.objects.none()

        if 'country' in self.data:
            try:
                country_id = int(self.data.get('country'))
                self.fields['city'].queryset = City.objects.filter(country_id=\
                    country_id).order_by().distinct()
            except (ValueError, TypeError):
                pass  # invalid input from the client; ignore and fallback to empty City queryset
        elif self.instance.pk:
            self.fields['city'].queryset = self.instance.country.city_set.order_by('name')

views.py

from django.shortcuts import render
from django.views.generic import ListView, CreateView, UpdateView
from django.urls import reverse_lazy

from .models import Person, City
from .forms import PersonForm

class PersonListView(ListView):
    model = Person
    context_object_name = 'people'

class PersonCreateView(CreateView):
    model = Person
    form_class = PersonForm
    success_url = reverse_lazy('person_changelist')

class PersonUpdateView(UpdateView):
    model = Person
    form_class = PersonForm
    success_url = reverse_lazy('person_changelist')

def load_cities(request):
    country_id = request.GET.get('country')
    cities = City.objects.filter(country_id=country_id).order_by('name')
    return render(request, 'hr/city_dropdown_list_options.html', {'cities': cities})

Upvotes: 2

Views: 1141

Answers (1)

Tyson
Tyson

Reputation: 424

When the database server runs an ordered distinct query, it will:

  1. Collect matching rows from storage, checking them against the WHERE clause.
  2. Sort according to the ORDER BY clause.
  3. Emit each row, where the row is DISTINCT from the previous (ordered) emitted row.

Since you have explicitly disabled ordering by not passing any field names to order_by(), the database is free to emit rows in whatever order it chooses (typically in the order they retrieved from storage).

DISTINCT filtering doesn't apply across the whole result set, it only applies against the preceding emitted row. And since your results are being emitted in an undefined (random) order, DISTINCT isn't likely to filter any results.

You must order your results accordingly by specifying field names in the order_by(*fields) part of your query.

Upvotes: 1

Related Questions