Django Database Access Optimization -

Django Database Access Optimization -

i have few question running in head given piece of code given below:

architects_list=[8757,8755,7066,8736,6961,6955,4830,6949,208,4876,59,115] clauses = ' '.join(['when id=%s %s' % (pk, i) i, pk in enumerate(architects_list)]) ordering = 'case %s end' % clauses architects = user.objects.filter(pk__in=architects_list).extra(select={'ordering': ordering}, order_by=('ordering',)) other_architects= user.objects.filter(iam='architect').exclude(pk__in=architects_list).annotate(pd =count('projectdetail')).order_by('-pd') archs_all = architects|other_architects

when concatenate architects , other_architects using '|', error "column 'id' in field list ambiguous" error @ archs_all = architects|other_architects. when utilize list(itertools.chain(architects,other_architects)) goes fine. don't want sec method suspect bloats memory.

does passing queryset 1000000 object paginator memory inefficient, if yes what's alternative?

traceback:

file "/home/harshai3/django/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response 114. response = wrapped_callback(request, *callback_args, **callback_kwargs) file "/home/harshai3/django/lib/python2.7/site-packages/django/views/generic/base.py" in view 69. homecoming self.dispatch(request, *args, **kwargs) file "/home/harshai3/django/lib/python2.7/site-packages/django/views/generic/base.py" in dispatch 87. homecoming handler(request, *args, **kwargs) file "/home/harshai3/django/zingyhomes_lateral/apps/project/views.py" in 1251. arch_objs = archs.page(1) file "/home/harshai3/django/lib/python2.7/site-packages/django/core/paginator.py" in page 50. number = self.validate_number(number) file "/home/harshai3/django/lib/python2.7/site-packages/django/core/paginator.py" in validate_number 39. if number > self.num_pages: file "/home/harshai3/django/lib/python2.7/site-packages/django/core/paginator.py" in _get_num_pages 86. if self.count == 0 , not self.allow_empty_first_page: file "/home/harshai3/django/lib/python2.7/site-packages/django/core/paginator.py" in _get_count 72. self._count = self.object_list.count() file "/home/harshai3/django/lib/python2.7/site-packages/django/db/models/query.py" in count 291. homecoming self.query.get_count(using=self.db) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/models/sql/query.py" in get_count 390. number = obj.get_aggregation(using=using)[none] file "/home/harshai3/django/lib/python2.7/site-packages/django/db/models/sql/query.py" in get_aggregation 356. result = query.get_compiler(using).execute_sql(single) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql 781. cursor.execute(sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/debug_toolbar/panels/sql/tracking.py" in execute 174. homecoming self._record(self.cursor.execute, sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/debug_toolbar/panels/sql/tracking.py" in _record 104. homecoming method(sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/util.py" in execute 69. homecoming super(cursordebugwrapper, self).execute(sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/util.py" in execute 53. homecoming self.cursor.execute(sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/utils.py" in __exit__ 99. six.reraise(dj_exc_type, dj_exc_value, traceback) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/util.py" in execute 53. homecoming self.cursor.execute(sql, params) file "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute 124. homecoming self.cursor.execute(query, args) file "/home/harshai3/django/lib/python2.7/site-packages/mysqldb/cursors.py" in execute 201. self.errorhandler(self, exc, value) file "/home/harshai3/django/lib/python2.7/site-packages/mysqldb/connections.py" in defaulterrorhandler 36. raise errorclass, errorvalue exception type: operationalerror @ /find-architects/ exception value: (1052, "column 'id' in field list ambiguous")

i think id in clauses causes ambiguity, both user table , project detail table have id field.

you can avoid ambiguity explicitly defining table name:

clauses = ' '.join(['when %s.id=%s %s' % (user._meta.db_table, pk, i) i, pk in enumerate(architects_list)])

however, don't think solve of problems. querysets annotations can not combined, think annotations of sec queryset lost (though i'm not 100% sure how works). combined ordering of 2 differently ordered querysets cannot combined as-is.

your query can combined single query, if specify default 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 %s' % (pk, i) i, pk in enumerate(architects_list)]) clauses += ' else 0' # or 999, depending on if want `other_architects` first or lastly 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'))

django django-queryset