Opened 2 years ago

Closed 2 years ago

#33792 closed Uncategorized (invalid)

Using QuerySet.extra works while RawSQL inside annotate fails

Reported by: Shane Ambler Owned by: nobody
Component: Database layer (models, ORM) Version: 4.0
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (1)

comment:1 by Mariusz Felisiak, 2 years ago

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top