Django sort objects with foreign key case insensitive

Question

I'm trying to sort a query according to a foreign key field case insensitive. My models are:

class Post(models.Model):
title = models.CharField(max_length = 80)
author = models.ForeignKey(User, default = User)
trade_option = models.CharField(max_length= 10)

class Book(models.Model):
book_title = models.CharField(max_length = 60)
post = models.ForeignKey(Post)

I want to sort my post objects according to the book_title field case insensitive.

I know if I want to sort case insensitive with the fields in Class Post I can just do:

posts = Post.objects.filter(trade_option= 'buy').extra(select = 
    {'lower_name' : 'lower( title )'}).order_by('lower_name')

However, when I try the same technique with sorting with foreign key book with book_title:

posts = Post.objects.filter(trade_option= 'buy').extra(select = 
    {'lower_name' : 'lower( book__book_title )'}).order_by('lower_name')

I get the error "no such column: book__boot_title"

I would like to know where I'm doing this wrong. Thanks

Ola Nguyen Van · Accepted Answer · 2015-08-26 08:03:25Z

1

I had the same problem. Here's what you should do:

from django.db.models.functions import Lower

posts = Post.objects.filter(trade_option= 'buy').order_by(Lower('title'))

edited Aug 26, 2015 at 8:03

answered Aug 20, 2015 at 9:12

Ola Nguyen Van

4514 silver badges6 bronze badges

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

1 Comment

mingle Over a year ago

Good answer, but keep in mind this is new in Django 1.8

knbk · Accepted Answer · 2015-01-18 15:38:49Z

0

A related lookup like book__book_title only works in the context of Django. Anything in extra is not processed by Django, so these lookups won't work. You have to explicitly fetch the result from the related table.

Please note that the ordering you're trying to define is most likely not what you expect it to be: a Post object can have multiple related Book objects. Ordering by book__book_title (manually or through the orm) can give you either an indeterminate ordering or duplicate rows, depending on the exact details of your query. You need to define which related book_title the posts should be ordered by.

answered Jan 18, 2015 at 15:38

knbk

53.9k9 gold badges130 silver badges128 bronze badges

Collectives™ on Stack Overflow

Django sort objects with foreign key case insensitive

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related