Using QuerySet.extra works while RawSQL inside annotate fails

[Shane Ambler]

Starting with a simplified model like this

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 -

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

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

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.

[Mariusz Felisiak]

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 support channels if you have further questions.