Sorting foreign key items in a list

Question

I have a Customer form which saves data to a table list with First name, surname, phone, email, address, etc. I have a sort function on the list that when the Title is clicked, it sorts that field from A-Z or Z-A. The problem is, I have an address fields which is a Foreign Key from a different model- it doesnt sort alphabetically, rather they sort using their respective primary key(pk).

The Address model contains Address, City, County, Postcode and Country and is returned to the table list in the following way: "Street Name, City, COUNTY, Country."

The Sort function is in my template.mako:

    <thead>
    <tr>
      <%
        # a list of column field names and labels
        column_fields = (
          ('first_name', 'First Name'),
          ('surname', 'Last Name'),              ,
          ('phone__number', 'Phone'),
          ('email__email', 'Email'),
          ('address', 'Address'),              
        )
        columns = ( {'field':x[0], 'label':x[1]} for x in column_fields )
      %>
      % for column in columns:            
        % if column['field'] == sort_field:
          <th><a href="${self.util.reverse('customers')}?sort=-  ${column['field']}">${column['label']}</th>
        % else:
          <th><a href="${self.util.reverse('customers')}?sort=${column['field']}">${column['label']}</th>
        % endif
      % endfor
    </tr>
  </thead>

Email and Phone are also Foreign keys, but each have one only field to get form their Models (email_email and phone_number).

There will always be a County selected ,as it is a dropdown and has a default value. So if the Address/city field is not submitted, I want to be able to sort by county. Whereas if the Address/city fields are filled out, I want to sort by them.

Any idea how to get the Address to sort

My views.py:

    def customers(request):    
        filtered = False
        sort_field = request.GET.get('sort', 'first_name')

        our_customers = Customer.objects.order_by(sort_field)
        form = SearchForm(request.POST or None, request.FILES or None)    

        if request.method == 'POST' and \
               form.is_valid() and \
               form.cleaned_data['search'] and \
               'clear-searchform' not in request.POST:
            search = form.cleaned_data['search']
            search_list = search.split()
            duplicated_customers = []
            for item in search_list:
            the_customers = Customer.objects.filter(
                Q(slug__icontains=item) |
                Q(first_name__icontains=item) |
                Q(surname__icontains=item) |
                Q(dob__icontains=item) |
                Q(phone__number__icontains=item) |
                Q(email__email__icontains=item) |
                Q(address__address__icontains=item) |
                Q(address__city__icontains=item) |
                Q(address__county__icontains=item) |
                Q(address__postcode__icontains=item) |
                Q(address__country__icontains=item) |
                Q(medical__icontains=item) |
                Q(notes__icontains=item)
                # need to amend so can search by organization
                )
                duplicated_customers = chain(duplicated_customers, the_customers)
                filtered = True
            our_customers = set(duplicated_customers)
        elif 'clear-searchform' in request.POST:
            form = SearchForm()

        dictionary = dict(
                form=form,
                customers=our_customers,
                sort_field=sort_field,
                filtered=filtered,
                display_name=_('Customers'),
                )
       return dictionary

The "Q()" is for a search function set up in the list.

Answer 1

To sort on a field from the ForeignKey model (e.g county) use the foreign model and field name separated by '__' (double underscore):

customers = Customer.objects.all().order_by('address__county')

https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by

To set the sort field conditionally:

if address.city != '':
    sort_field = 'address__city'
else:
    sort_field = 'address__county'
customers = Customer.objects.all().order_by(sort_field)