Django admin list sort error

[Derek]

I have recently discovered that Django admin is generating errors when trying to sort a table on certain fields: in particular, those fields that are ForeignKey linked to a separate table, each of which has a 'name' field.  Selecting any of these columns for a sort generates this error:

OperationalError at /admin/myapp/mytable/
(1060, "Duplicate column name 'name'")

A full stack trace is shown below.  I am working with MySQL 5.5 and Django 1.6.3.

Has anyone else encountered this error?  Is this a known bug and/or is there a workaround?

Thanks
Derek


Stack Trace:

Traceback:
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  139.                 response = response.render()
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/response.py" in render
  105.             self.content = self.rendered_content
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/response.py" in rendered_content
  82.         content = template.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  140.             return self._render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in _render
  134.         return self.nodelist.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  840.                 bit = self.render_node(node, context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render_node
  854.         return node.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  123.         return compiled_parent._render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in _render
  134.         return self.nodelist.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  840.                 bit = self.render_node(node, context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render_node
  854.         return node.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  123.         return compiled_parent._render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in _render
  134.         return self.nodelist.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  840.                 bit = self.render_node(node, context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render_node
  854.         return node.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  62.             result = block.nodelist.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  840.                 bit = self.render_node(node, context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render_node
  854.         return node.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  62.             result = block.nodelist.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  840.                 bit = self.render_node(node, context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render_node
  854.         return node.render(context)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/template/base.py" in render
  1196.                     _dict = func(*resolved_args, **resolved_kwargs)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/contrib/admin/templatetags/admin_list.py" in date_hierarchy
  312.                                                last=models.Max(field_name))
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/models/query.py" in aggregate
  278.         return query.get_aggregation(using=self.db)
File "/home/derek/.virtualenvs/demo/local/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/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  786.         cursor.execute(sql, params)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute
  69.             return super(CursorDebugWrapper, self).execute(sql, params)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute
  53.                 return self.cursor.execute(sql, params)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/utils.py" in __exit__
  99.                 six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute
  53.                 return self.cursor.execute(sql, params)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  124.             return self.cursor.execute(query, args)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  201.             self.errorhandler(self, exc, value)
File "/home/derek/.virtualenvs/demo/local/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  36.     raise errorclass, errorvalue

[Derek]

No one else? Strange...

The closest related issue I could find"out there" relates to the way that the MySQLdb cursor returns its results i.e. with the tablename prefixes stripped off.  So fields called 'name' or 'code' (that commonly appear in many tables) all now clash with each other.  

I tried one approach, which is to monkey patch in the use of connection.cursor(MySQLdb.cursors.DictCursor) but Django was not happy with that at all!  It clearly does not want a dictionary.

Any other suggestions or workarounds - short of my ensuring unique field names through out my database? (which is a fair amount of rework).

Thanks

Derek

[Collin Anderson]

do you want to paste in your models?

[Derek]

Well, this is an "internal" project ... but a "translation" of some of the fields of some of the models would look something those below (NB this is a subset with just a few models - and any 'grammatical' errors are from the "translation"  - the originals work perfectly in every respect except for the occasional sort error.)

So in the example below, if I sort the Site on 'Place' via admin, for example, then I get:

OperationalError at /admin/.../...

(1060, "Duplicate column name 'name'")

# EXAMPLE ONLY

from django.db.models import ForeignKey, Model

from django.db.models import AutoField,  CharField

# in 'areas' app

class Region(Model):

    """A defined area, usually established or created by an official body.

    """

    id = AutoField(primary_key=True)

    name = CharField(max_length=100)

    code = CharField(unique=True, max_length=25)

    description = CharField(blank=True, null=True, max_length=250)

class Place(Model):

    """An owned place or area.

    """

    id = AutoField(primary_key=True)

    name = CharField(max_length=100)

    description = CharField(blank=True, null=True, max_length=250)

    region = ForeignKey(Region)

# in 'core' app

from areas.models import Place, Region

class Site(Model):

    """Contains the core details and attributes.

    """

    id = AutoField(primary_key=True)

    code = CharField(unique=True, max_length=100)

    place = ForeignKey(Place)

On 4 September 2014 20:24, Collin Anderson <cmawe...@gmail.com> wrote:

do you want to paste in your models?

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/wotonl4i01U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/16396c5b-6b98-441d-9026-1c29c9fe73cd%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Collin Anderson]

try sorting by place__name instead.

[Derek]

Sorry Colin, I have no idea what you mean.  Where in the django admin settings would I specify this?  AFAIK, the column names / labels / IDs  are auto-generated by the admin and it attempts the sort when you click on the column heading.

On 4 September 2014 21:19, Collin Anderson <cmawe...@gmail.com> wrote:

try sorting by place__name instead.

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/wotonl4i01U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/b2292afe-5451-4d2b-9a27-d9deb8f10058%40googlegroups.com.

[Collin Anderson]

It guess it's pretty complicated, but it's possible to specify it kind of like this:

class MyAdmin(admin.ModelAdmin):

    fields = ['name', 'theplace']

    def theplace(self, obj):

        return obj.place.name

    theplace.admin_order_field = ['place__name']

[Derek]

Colin

Good suggestion, but I already have a stack of "extra" fields like this (for special needs e.g. calculated  or reformatted fields).  However, doing this for normal fields that Should Just Work seems like a real hack.  Surely the answer is that the underlying SQL is not being generated properly?

Derek

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/wotonl4i01U/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/74dba43b-9462-4bbe-96a6-7672ddd3fbd6%40googlegroups.com.

[Collin Anderson]

Yes, it does sound like a bug. Can you reproduce it with a minimal project?