Re: QuerySet.extra and ticket #28756

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

On Monday, October 30, 2017 at 3:25:29 PM UTC-4, Brandon wrote:

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/81d1ef7b-a77b-48bc-be81-4b451579c6e4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.