Django数据库访问优化

Question

对于下面这段给定的代码，我有几个问题:

    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)])
    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

1. 当我使用'|'连接架构师和other_architect时，我在archs_all = architects|other_architects得到一个错误"字段列表中的列'id'是模糊的"错误。当我使用list(itertools.chain(architects,other_architects))时，一切都很好。我不想要第二个方法，因为我怀疑它会增加内存。

2. 传递一个查询集说一个百万对象分页器是内存效率低下，如果是，什么是替代方案?

回溯:

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.             return self.dispatch(request, *args, **kwargs)
File "/home/harshai3/django/lib/python2.7/site-packages/django/views/generic/base.py" in dispatch
  87.         return handler(request, *args, **kwargs)
File "/home/harshai3/django/zingyhomes_lateral/apps/project/views.py" in get
  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 and 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.         return 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.         return 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.             return method(sql, params)
File "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/util.py" in execute
  69.             return super(CursorDebugWrapper, self).execute(sql, params)
File "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/util.py" in execute
  53.                 return 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.                 return self.cursor.execute(sql, params)
File "/home/harshai3/django/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  124.             return 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 at /find-architects/
Exception Value: (1052, "Column 'id' in field list is ambiguous")

Answer 1

我认为您的clauses中的id导致了歧义，因为用户表和项目详细信息表都有一个id字段。

可以通过显式定义表名来避免这种歧义:

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

然而，我不认为这将解决你所有的问题。带有注释的查询集通常不能合并，我认为第二个查询集的注释总是丢失(尽管我不能100%确定它是如何工作的)。两个不同排序的查询集的组合排序不能按原样组合。

如果您为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'))