[Django] #33792: Using QuerySet.extra works while RawSQL inside annotate fails

4 views

Skip to first unread message

Django

unread,

Jun 20, 2022, 3:42:26 AM6/20/22

to django-...@googlegroups.com

#33792: Using QuerySet.extra works while RawSQL inside annotate fails
-------------------------------------+-------------------------------------
Reporter: Shane | Owner: nobody
Ambler |
Type: | Status: new
Uncategorized |
Component: Database | Version: 4.0
layer (models, ORM) |
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Starting with a simplified model like this
{{{#!python
class Materials(models.Model):
title = models.TextField()

class Quotes(models.Model):
source = models.ForeignKey('Materials', on_delete=models.CASCADE)
quote = models.TextField()
}}}

I then create a form -
{{{#!python
class QuoteForm(forms.Form):
source =
forms.ModelChoiceField(queryset=Materials.objects.order_by('title').all())
quote = forms.CharField(widget=forms.Textarea())

def clean_source(self):
print('Cleaning source:', self.cleaned_data)
return self.cleaned_data['source']
}}}

At this point, all works well, but the `title` often starts with a numeric
string and I want to change the sort order so that `2nd congress` sorts
before `12th congress`.

Using postgresql, I can change the `source` line to
{{{#!python
source =
forms.ModelChoiceField(queryset=Materials.objects.annotate(num_order=RawSQL('''cast(substring(title
from '^([0-9]{1,10})') as integer)''',
('',))).order_by('num_order','title'))
}}}

and this provides the forms select list sorted the way I want but the form
fails to validate with source being an invalid choice. I added the
`clean_source()` method above to also indicate that using `RawSQL` here
fails to call the `clean_source` method.

If I change the source line to use `QuerySet.extra()` -
{{{#!python
source =
forms.ModelChoiceField(queryset=Materials.objects.extra(select={'num_order':
'''cast(substring(title from '^([0-9]{1,10})') as
integer)'''}).order_by('num_order','title'))
}}}

I get the sort order, form validation works and `clean_source()` gets
called.

--
Ticket URL: <https://code.djangoproject.com/ticket/33792>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jun 20, 2022, 4:34:52 AM6/20/22

to django-...@googlegroups.com

#33792: Using QuerySet.extra works while RawSQL inside annotate fails
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

Thanks for this report, however it's an issue in your code not in Django
itself. It works with empty `params` (instead of a blank string):
{{{

num_order=RawSQL('''cast(substring(title from '^([0-9]{1,10})') as

integer)''', params=())).order_by('num_order','title')
}}}
Please use one of
[https://code.djangoproject.com/wiki/TicketClosingReasons/UseSupportChannels
support channels] if you have further questions.