QuerySet.extra and ticket #28756

[Brandon]

In my original ticket (https://code.djangoproject.com/ticket/28756) I explained this:

I have reoccurring situation, when in a view.py function I need a queryset ordered by a set of identifiers. The order of those identifiers is dependent on factors outside of my control. Essentially in my view I am using modelformset_factory to generate a modelformset, then the usage of that modelformset instance requires queryset. It is that final queryset that must be ordered by the identifier whose order is externally determined.
So I use the .extra( ) function like below:

quali_results = ToxicologyCaseQualitativeResult.objects.filter(case_id=case.id).select_related('test').order_by('test__name')

field_list = ["AMPHS","BARB","BNZ","BE","Ecstasy","METH","6AM","PCP","THC"] #Usually populated via retrieval of user settings.
if len(field_list) > 0:
    field_list = field_list + [q.test.identifier for q in quali_results] fields = '"'+'","'.join(str(field) for field in field_list)+'"'
    field_sql = "FIELD(`identifier`,"+fields+")"
    quali_results = quali_results.extra(select={'field_sql' : field_sql}, order_by=['field_sql'])

quali_resultfs = QualitativeResultFormset(queryset=quali_results, prefix='quali_results')

This was closed by Tim Graham stating that I should be using "Query Expression", which is fine. So the code became this:

from django.db.models import Value, CharField

quali_results = ToxicologyCaseQualitativeResult.objects.filter(case_id=case.id).select_related('test')

field_list = ["AMPHS","BARB","BNZ","BE","Ecstasy","METH","6AM","PCP","THC"] #Usually populated via retrieval of user settings.
if len(field_list) > 0:
    field_list = field_list + [q.test.identifier for q in quali_results] fields = '"'+'","'.join(str(field) for field in field_list)+'"'
    field_sql = "FIELD(`identifier`,"+fields+")"
    quali_results = quali_results.order_by(Value("FIELD(`identifier`,"+fields+")",output_field=CharField()))

quali_resultfs = QualitativeResultFormset(queryset=quali_results, prefix='quali_results')

Which I agree is could be seen as better, and it doesn't use the Querset.extra( ) method. The only problem is that it doesn't work.

The first method generates this SQL:

SELECT (FIELD(`identifier`,"AMPHS","BARB","BNZ","BE","Ecstasy","METH","6AM","PCP","THC","AMPHS","BARB","BNZ","THC","BE","Ecstasy","METH","6AM","PCP")) AS `field_sql`, `toxicology_toxicologycasequalitativeresult`.`id`, `toxicology_toxicologycasequalitativeresult`.`case_id`, `toxicology_toxicologycasequalitativeresult`.`test_id`, `toxicology_toxicologycasequalitativeresult`.`detection`, `toxicology_toxicologycasequalitativeresult`.`result`, `toxicology_toxicologycasequalitativeresult`.`concentration`, `toxicology_toxicologycasequalitativeresult`.`outcome`, `toxicology_toxicologycasequalitativeresult`.`cutoff`, `toxicology_toxicologycasequalitativeresult`.`units`, `toxicology_toxicologycasequalitativeresult`.`comments`, `toxicology_toxicologyqualitativetestreference`.`id`, `toxicology_toxicologyqualitativetestreference`.`panel_id`, `toxicology_toxicologyqualitativetestreference`.`enabled`, `toxicology_toxicologyqualitativetestreference`.`name`, `toxicology_toxicologyqualitativetestreference`.`identifier`, `toxicology_toxicologyqualitativetestreference`.`analyte_id`, `toxicology_toxicologyqualitativetestreference`.`LOB`, `toxicology_toxicologyqualitativetestreference`.`LOD`, `toxicology_toxicologyqualitativetestreference`.`LOQ`, `toxicology_toxicologyqualitativetestreference`.`ULOL`, `toxicology_toxicologyqualitativetestreference`.`expression`, `toxicology_toxicologyqualitativetestreference`.`true_result`, `toxicology_toxicologyqualitativetestreference`.`false_result`, `toxicology_toxicologyqualitativetestreference`.`true_outcome`, `toxicology_toxicologyqualitativetestreference`.`false_outcome`, `toxicology_toxicologyqualitativetestreference`.`concentration`, `toxicology_toxicologyqualitativetestreference`.`cutoff`, `toxicology_toxicologyqualitativetestreference`.`units`, `toxicology_toxicologyqualitativetestreference`.`method`, `toxicology_toxicologyqualitativetestreference`.`description` FROM `toxicology_toxicologycasequalitativeresult` INNER JOIN `toxicology_toxicologyqualitativetestreference` ON (`toxicology_toxicologycasequalitativeresult`.`test_id` = `toxicology_toxicologyqualitativetestreference`.`id`) WHERE `toxicology_toxicologycasequalitativeresult`.`case_id` = 239 ORDER BY `field_sql` ASC

The second method generates this SQL:

SELECT `toxicology_toxicologycasequalitativeresult`.`id`, `toxicology_toxicologycasequalitativeresult`.`case_id`, `toxicology_toxicologycasequalitativeresult`.`test_id`, `toxicology_toxicologycasequalitativeresult`.`detection`, `toxicology_toxicologycasequalitativeresult`.`result`, `toxicology_toxicologycasequalitativeresult`.`concentration`, `toxicology_toxicologycasequalitativeresult`.`outcome`, `toxicology_toxicologycasequalitativeresult`.`cutoff`, `toxicology_toxicologycasequalitativeresult`.`units`, `toxicology_toxicologycasequalitativeresult`.`comments`, `toxicology_toxicologyqualitativetestreference`.`id`, `toxicology_toxicologyqualitativetestreference`.`panel_id`, `toxicology_toxicologyqualitativetestreference`.`enabled`, `toxicology_toxicologyqualitativetestreference`.`name`, `toxicology_toxicologyqualitativetestreference`.`identifier`, `toxicology_toxicologyqualitativetestreference`.`analyte_id`, `toxicology_toxicologyqualitativetestreference`.`LOB`, `toxicology_toxicologyqualitativetestreference`.`LOD`, `toxicology_toxicologyqualitativetestreference`.`LOQ`, `toxicology_toxicologyqualitativetestreference`.`ULOL`, `toxicology_toxicologyqualitativetestreference`.`expression`, `toxicology_toxicologyqualitativetestreference`.`true_result`, `toxicology_toxicologyqualitativetestreference`.`false_result`, `toxicology_toxicologyqualitativetestreference`.`true_outcome`, `toxicology_toxicologyqualitativetestreference`.`false_outcome`, `toxicology_toxicologyqualitativetestreference`.`concentration`, `toxicology_toxicologyqualitativetestreference`.`cutoff`, `toxicology_toxicologyqualitativetestreference`.`units`, `toxicology_toxicologyqualitativetestreference`.`method`, `toxicology_toxicologyqualitativetestreference`.`description` FROM `toxicology_toxicologycasequalitativeresult` INNER JOIN `toxicology_toxicologyqualitativetestreference` ON (`toxicology_toxicologycasequalitativeresult`.`test_id` = `toxicology_toxicologyqualitativetestreference`.`id`) WHERE `toxicology_toxicologycasequalitativeresult`.`case_id` = 239 ORDER BY FIELD(`identifier`,"AMPHS","BARB","BNZ","BE","Ecstasy","METH","6AM","PCP","THC","AMPHS","BARB","BNZ","THC","BE","Ecstasy","METH","6AM","PCP") ASC

Both of which, if run at the mysql command line or typed into your favorite mysql client produce the same *correct* output. However, the first method works when in the view where I'm attempting to use it. The second method does not, and the entries simply end up being sorted by `toxicology_toxicologycasequalitativeresult`.`id`. It is almost as if the field to be used for sorting must be the first field.

I have tried the second method with annotations, then an order_by on that annotation. I have tried using various combinations of ExpressionWrapper, Value and even Func to no available. In all instances the SQL is perfect - even quali_results.ordered = True - but the queryset always fails to order the results. Only by using the first method do I get properly sorted, properly ordered, results in the queryset.

Any help is appreciated.

--Brandon

[Tim Graham]

You aren't using Value correctly -- it's not for wrapping raw SQL. https://docs.djangoproject.com/en/dev/ref/models/expressions/#value-expressions

What I was trying to suggest is that you write your own expression class that generates the FIELD(...) SQL.
https://docs.djangoproject.com/en/dev/ref/models/expressions/#writing-your-own-query-expressions