Problem with behaviour of ManyToManyField

[guneeyoufix]

I have a problem using the ManyToManyFied with an already existing database. I have two tables called AVAILABILITY and TESTDEF that are linked together according to a n-n relationship. I have defined them as following in my models.py file (with the help of inspectdb). The names of the columns in the tables are the same as the python names.

class Testdef(models.Model):
    testid = models.DecimalField(max_digits=38, decimal_places=0, primary_key=True)
    testname = models.CharField(max_length=100)
    testtitle = models.CharField(max_length=255, blank=True)
    testabbr = models.CharField(max_length=50, blank=True)
    def __unicode__(self):
        return self.testname+' ('+self.testabbr+')'
    class Meta:
        db_table = u'TESTDEF'

class Availability(models.Model):
    availabilityid = models.AutoField("Id", primary_key=True, null=False)
    availabilityname = models.CharField("Name", max_length=255)
    bdiiflag = models.CharField("BDii", max_length=1, choices=(('Y', "Yes"),('N', "No"),))
    void = models.ForeignKey(Vo, db_column='void')
    tests = models.ManyToManyField(Testdef, db_table='AVAILABILITYTESTCRITICALITY', related_name='availabilityid')
    def __unicode__(self):
        return self.availabilityname
    class Meta:
        db_table = u'AVAILABILITY'
        verbose_name_plural = u'Availabilities'

The linking table is called AVAILABILITYTESTCRITICALITY, and has the following fields: AVAILABILITYID, TESTID

I use this environment:

Environment:

Request Method: GET
Request URL: http://localhost:8000/admin/avl/availability/6/
Django Version: 1.0-alpha_2-SVN-unknown
Python Version: 2.3.4
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.sites',
 'django.contrib.admin',
 'django.contrib.databrowse',
 'mysite.avl']
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.middleware.doc.XViewMiddleware')

The code as given above, doesn't work, and I get this error when trying to access an AVAILABILITY item from the admin application:

Traceback:
File "/usr/lib/python2.3/site-packages/django/core/handlers/base.py" in get_response
  86.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/sites.py" in root
  172.                 return self.model_page(request, *url.split('/', 2))
File "/usr/lib/python2.3/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/sites.py" in model_page
  189.         return admin_obj(request, rest_of_url)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/options.py" in __call__
  275.             return self.change_view(request, unquote(url))
File "/usr/lib/python2.3/site-packages/django/db/transaction.py" in _commit_on_success
  198.                 res = func(*args, **kw)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/options.py" in change_view
  657.             form = ModelForm(instance=obj)
File "/usr/lib/python2.3/site-packages/django/forms/models.py" in __init__
  197.             object_data = model_to_dict(instance, opts.fields, opts.exclude)
File "/usr/lib/python2.3/site-packages/django/forms/models.py" in model_to_dict
  117.                 data[f.name] = [obj.pk for obj in f.value_from_object(instance)]
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in _result_iter
  176.                 self._fill_cache()
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in _fill_cache
  604.                     self._result_cache.append(self._iter.next())
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in iterator
  266.         for row in self.query.results_iter():
File "/usr/lib/python2.3/site-packages/django/db/models/sql/query.py" in results_iter
  200.         for rows in self.execute_sql(MULTI):
File "/usr/lib/python2.3/site-packages/django/db/models/sql/query.py" in execute_sql
  1614.         cursor.execute(sql, params)
File "/usr/lib/python2.3/site-packages/django/db/backends/util.py" in execute
  19.             return self.cursor.execute(sql, params)
File "/usr/lib/python2.3/site-packages/django/db/backends/oracle/base.py" in execute
  343.         return Database.Cursor.execute(self, query, self._param_generator(params))

Exception Type: DatabaseError at /admin/avl/availability/6/
Exception Value: ORA-00904: "AVAILABILITYTESTCRITICALITY"."TESTDEF_ID": invalid identifier

After renaming the TESTID field in my linking table as TESTDEF_ID, I get this other error, as if django had completely forgotten about the primary keys I have defined before (it seems that the field it tries to access is the default name of the primary key).

Traceback:
File "/usr/lib/python2.3/site-packages/django/core/handlers/base.py" in get_response
  86.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/sites.py" in root
  172.                 return self.model_page(request, *url.split('/', 2))
File "/usr/lib/python2.3/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/sites.py" in model_page
  189.         return admin_obj(request, rest_of_url)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/options.py" in __call__
  275.             return self.change_view(request, unquote(url))
File "/usr/lib/python2.3/site-packages/django/db/transaction.py" in _commit_on_success
  198.                 res = func(*args, **kw)
File "/usr/lib/python2.3/site-packages/django/contrib/admin/options.py" in change_view
  657.             form = ModelForm(instance=obj)
File "/usr/lib/python2.3/site-packages/django/forms/models.py" in __init__
  197.             object_data = model_to_dict(instance, opts.fields, opts.exclude)
File "/usr/lib/python2.3/site-packages/django/forms/models.py" in model_to_dict
  117.                 data[f.name] = [obj.pk for obj in f.value_from_object(instance)]
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in _result_iter
  176.                 self._fill_cache()
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in _fill_cache
  604.                     self._result_cache.append(self._iter.next())
File "/usr/lib/python2.3/site-packages/django/db/models/query.py" in iterator
  266.         for row in self.query.results_iter():
File "/usr/lib/python2.3/site-packages/django/db/models/sql/query.py" in results_iter
  200.         for rows in self.execute_sql(MULTI):
File "/usr/lib/python2.3/site-packages/django/db/models/sql/query.py" in execute_sql
  1614.         cursor.execute(sql, params)
File "/usr/lib/python2.3/site-packages/django/db/backends/util.py" in execute
  19.             return self.cursor.execute(sql, params)
File "/usr/lib/python2.3/site-packages/django/db/backends/oracle/base.py" in execute
  343.         return Database.Cursor.execute(self, query, self._param_generator(params))

Exception Type: DatabaseError at /admin/avl/availability/6/
Exception Value: ORA-00904: "AVAILABILITYTESTCRITICALITY"."TESTDEF_ID": invalid identifier

Is it just me (maybe I have missed an option in the definition of the ManyToManyField), or is it a real bug?

[Malcolm Tredinnick]

There's no bug here. By default, Django wants the column names of the join table between models Foo and Bar to be foo_id and bar_id. The columns of your intermediate table aren't called that, so the error is being raised when trying to execute the SQL on the intermediate (join) table.

To solve this, construct the intermediate table manually, specifying the names of the db columns and use the new through parameter on ManyToManyField to specify the intermediate model.