[Answer]-Django Database Access Optimization

by

1👍

✅

I think the id in your clauses causes the ambiguity, as both the user table and the project detail table have an id field.

You can avoid this ambiguity by explicitly defining the table name:

clauses = ' '.join(['WHEN %s.id=%s THEN %s' % (User._meta.db_table, pk, i) for i, pk in enumerate(architects_list)])

However, I don’t think this will solve all of your problems. Querysets with annotations can generally not be combined, I think the annotations of the second queryset are always lost (though I’m not 100% sure how it works). The combined ordering of two differently ordered querysets cannot be combined as-is.

Your query can be combined into a single query, if you specify a default for the SQL CASE:

from django.db.models import Q

architects_list=[8757,8755,7066,8736,6961,6955,4830,6949,208,4876,59,115]
clauses = ' '.join(['WHEN id=%s THEN %s' % (pk, i) for i, pk in enumerate(architects_list)])
clauses += ' ELSE 0' # or 999, depending on if you want the `other_architects` first or last
ordering = 'CASE %s END' % clauses
architects = (User.objects.filter(Q(id__in=architects_list) | Q(Iam='Architect'))
              .extra(select={'ordering': ordering})
              .annotate(pd=Count('projectdetail'))
              .order_by('ordering', '-pd'))
👤knbk

Leave a comment