Opened 4 years ago

Closed 4 years ago

#15108 closed (worksforme)

order_by on annotate doesn't work with camelCased annotation names

Reported by: jorn Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

this works:

User.objects.annotate(gamecount=Count('game')).order_by('-gamecount')

this doesn't:

User.objects.annotate(gameCount=Count('game')).order_by('-gameCount')

It throws an exception:

ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (267, 0))

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)

/home/django/gwapy/src/mysite/<ipython console> in <module>()

/usr/lib/pymodules/python2.6/IPython/Prompts.pyc in __call__(self, arg)
    550 
    551             # and now call a possibly user-defined print mechanism

--> 552             manipulated_val = self.display(arg)
    553 
    554             # user display hooks can change the variable to be stored in


/usr/lib/pymodules/python2.6/IPython/Prompts.pyc in _display(self, arg)
    576             return IPython.generics.result_display(arg)
    577         except TryNext:
--> 578             return self.shell.hooks.result_display(arg)
    579 
    580     # Assign the default display method:


/usr/lib/pymodules/python2.6/IPython/hooks.pyc in __call__(self, *args, **kw)
    139             #print "prio",prio,"cmd",cmd #dbg

    140             try:
--> 141                 ret = cmd(*args, **kw)
    142                 return ret
    143             except ipapi.TryNext, exc:

/usr/lib/pymodules/python2.6/IPython/hooks.pyc in result_display(self, arg)
    169 
    170     if self.rc.pprint:
--> 171         out = pformat(arg)
    172         if '\n' in out:
    173             # So that multi-line strings line up with the left column of


/usr/lib/python2.6/pprint.pyc in pformat(self, object)
    109     def pformat(self, object):
    110         sio = _StringIO()
--> 111         self._format(object, sio, 0, 0, {}, 0)
    112         return sio.getvalue()
    113 

/usr/lib/python2.6/pprint.pyc in _format(self, object, stream, indent, allowance, context, level)
    127             self._readable = False
    128             return
--> 129         rep = self._repr(object, context, level - 1)
    130         typ = _type(object)
    131         sepLines = _len(rep) > (self._width - 1 - indent - allowance)

/usr/lib/python2.6/pprint.pyc in _repr(self, object, context, level)
    221     def _repr(self, object, context, level):
    222         repr, readable, recursive = self.format(object, context.copy(),
--> 223                                                 self._depth, level)
    224         if not readable:                                                                                                                                                                                                                                              
    225             self._readable = False

/usr/lib/python2.6/pprint.pyc in format(self, object, context, maxlevels, level)
    233         and whether the object represents a recursive construct.
    234         """
--> 235         return _safe_repr(object, context, maxlevels, level)
    236 
    237 

/usr/lib/python2.6/pprint.pyc in _safe_repr(object, context, maxlevels, level)
    318         return format % _commajoin(components), readable, recursive
    319 
--> 320     rep = repr(object)
    321     return rep, (rep and not rep.startswith('<')), False
    322 

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.pyc in __repr__(self)
     65 
     66     def __repr__(self):
---> 67         data = list(self[:REPR_OUTPUT_SIZE + 1])
     68         if len(data) > REPR_OUTPUT_SIZE:
     69             data[-1] = "...(remaining elements truncated)..."

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.pyc in __len__(self)
     80                 self._result_cache = list(self.iterator())
     81         elif self._iter:
---> 82             self._result_cache.extend(list(self._iter))
     83         return len(self._result_cache)
     84 

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/query.pyc in iterator(self)
    269         model = self.model
    270         compiler = self.query.get_compiler(using=db)
--> 271         for row in compiler.results_iter():
    272             if fill_cache:
    273                 obj, _ = get_cached_row(model, row,

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/sql/compiler.pyc in results_iter(self)
    675         fields = None
    676         has_aggregate_select = bool(self.query.aggregate_select)
--> 677         for rows in self.execute_sql(MULTI):
    678             for row in rows:
    679                 if resolve_columns:

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/models/sql/compiler.pyc in execute_sql(self, result_type)
    730 
    731         cursor = self.connection.cursor()
--> 732         cursor.execute(sql, params)
    733 
    734         if not result_type:

/usr/local/lib/python2.6/dist-packages/Django-1.2.4-py2.6.egg/django/db/backends/postgresql_psycopg2/base.pyc in execute(self, query, args)
     42     def execute(self, query, args=None):
     43         try:
---> 44             return self.cursor.execute(query, args)
     45         except Database.IntegrityError, e:
     46             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

DatabaseError: column "gamecount" does not exist
LINE 1: ...", "myapp_user"."lastSeen" ORDER BY gameCount ...

Are there maybe quotes missing around the order by clause's gameCount?

Just in case it matters:
Game has a field users = ManyToManyField(User)

Change History (1)

comment:1 Changed 4 years ago by kmtracey

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to worksforme
  • Status changed from new to closed

There is something beyond what is mentioned here going on. Is it not as simple as camel case in annotation names. Taking contrib models Group and Permission that have the same ManyToMany relationship as your Game and User:

>>> from django.db import connection
>>> from django.db.models import Count
>>> from django.contrib.auth.models import Permission
>>> x = list(Permission.objects.annotate(groupcount=Count('group')).order_by('-groupcount'))
>>> connection.queries[-1]
{'time': '0.003', 'sql': u'SELECT `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`,
 `auth_permission`.`codename`, COUNT(`auth_group_permissions`.`group_id`) AS `groupcount` FROM `auth_permission` LEFT OU
TER JOIN `auth_group_permissions` ON (`auth_permission`.`id` = `auth_group_permissions`.`permission_id`) GROUP BY `auth_
permission`.`id`, `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`, `auth_permissio
n`.`codename` ORDER BY groupcount DESC'}
>>> x = list(Permission.objects.annotate(groupCount=Count('group')).order_by('-groupCount'))
>>> connection.queries[-1]
{'time': '0.001', 'sql': u'SELECT `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`,
 `auth_permission`.`codename`, COUNT(`auth_group_permissions`.`group_id`) AS `groupCount` FROM `auth_permission` LEFT OU
TER JOIN `auth_group_permissions` ON (`auth_permission`.`id` = `auth_group_permissions`.`permission_id`) GROUP BY `auth_
permission`.`id`, `auth_permission`.`id`, `auth_permission`.`name`, `auth_permission`.`content_type_id`, `auth_permissio
n`.`codename` ORDER BY groupCount DESC'}
>>>

Tried on both the reported version (1.2) and trunk. You might find further clues by taking a look at the full SQL query that is being executed for the query. If you can give some more clues as to what else is coming into play here feel free to re-open the ticket, but with the information provided so far I cannot recreate so am closing worksforme for now.

Note: See TracTickets for help on using tickets.
Back to Top