id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33792	Using QuerySet.extra works while RawSQL inside annotate fails	Shane Ambler	nobody	"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.
"	Uncategorized	closed	Database layer (models, ORM)	4.0	Normal	invalid	QuerySet.extra		Unreviewed	0	0	0	0	0	0