Wrong behavior of ManyToMany relationship while using 'trough' in ManyToManyField and 'db_column' and 'to_field' properties in intermediate model

[Alexander Kaidalov]

Models

# coding: utf-8

from django.db import models

class NagiosHosts(models.Model):
    class Meta:
        db_table = 'nagios_hosts'
        managed = False

    host_id = models.IntegerField(primary_key = True)
    host_object_id = models.IntegerField()
    alias = models.CharField(max_length = 255)
    display_name = models.CharField(max_length = 255)
    address = models.CharField(max_length = 128)

class NagiosHostStatus(models.Model):
    class Meta:
        db_table = 'nagios_hoststatus'
        managed = False

    hoststatus_id = models.IntegerField(primary_key = True)
    host_object_id = models.IntegerField()
    hosts = models.ForeignKey('NagiosHosts', db_column = 'host_object_id', to_field = 'host_object_id')
    last_check = models.DateTimeField()
    current_state = models.IntegerField()
    output = models.CharField(max_length = 255)

class NagiosHostGroups(models.Model):
    class Meta:
        db_table = 'nagios_hostgroups'
        managed = False

    hostgroup_id = models.IntegerField(primary_key = True)
    hostgroup_object_id = models.IntegerField()
    alias = models.CharField(max_length = 255)
    members = models.ManyToManyField('NagiosHosts', through = 'NagiosHostGroupMembers')

class NagiosHostGroupMembers(models.Model):
    class Meta:
        db_table = 'nagios_hostgroup_members'
        managed = False

    hostgroup_member_id = models.IntegerField(primary_key = True)
    hostgroup_id = models.IntegerField()
    host_object_id = models.IntegerField()
    group = models.ForeignKey('NagiosHostGroups', db_column = 'hostgroup_id', to_field = 'hostgroup_id')
    host = models.ForeignKey('NagiosHosts', db_column = 'host_object_id', to_field = 'host_object_id')

Wrong Behavior (a.k.a Querying DB)

Python 2.5.2 (r252:60911, Jan  4 2009, 17:40:26) 
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from djnag.ndo2dj.models import *
>>> from django.db import connection
>>> g1 = NagiosHostGroups.objects.all()[0]
>>> print g1.alias
Switches
>>> g1.members.all()
[]
>>> connection.queries
[{'time': '0.001', 'sql': u'SELECT `nagios_hosts`.`host_id`, `nagios_hosts`.`host_object_id`, `nagios_hosts`.`alias`, `nagios_hosts`.`display_name`, `nagios_hosts`.`address` FROM `nagios_hosts` INNER JOIN `nagios_hostgroup_members` ON (`nagios_hosts`.`host_id` = `nagios_hostgroup_members`.`host_object_id`) WHERE `nagios_hostgroup_members`.`hostgroup_id` = 53  LIMIT 21'}]

Resulting (Wrong!!!) SQL query

SELECT `nagios_hosts`.`host_id`, `nagios_hosts`.`host_object_id`, `nagios_hosts`.`alias`, `nagios_hosts`.`display_name`, `nagios_hosts`.`address` 
FROM `nagios_hosts` 
INNER JOIN `nagios_hostgroup_members` ON (`nagios_hosts`.`host_id` = `nagios_hostgroup_members`.`host_object_id`) 
WHERE `nagios_hostgroup_members`.`hostgroup_id` = 53  
LIMIT 21

Here Django relies on primary key of NagiosHosts (nagios_hosts.host_id = nagios_hostgroup_members.host_object_id) rather than on to_field = host_object_id set in intermediate model. And I think this behavior is wrong. It should use to_field setting.

[Russell Keith-Magee]

It would be great if you could reduce this to a *minimal* test case -- that is, the simplest set of models that demonstrate the problem. I have no difficulty imagining that the m2m refactor changes have caused problems with complex m2m-through queries, but dumping the entire internals of Nagios doesn't help us track down the specific problem.

[Alexander Kaidalov]

New, stripped down and abstract version of models:

from django.db import models

class Item(models.Model):
    unique_field = models.IntegerField(unique = True)
    title = models.CharField(max_length = 10) 

class Group(models.Model):
    title = models.CharField(max_length = 10) 
    members = models.ManyToManyField('Item', through = 'GroupItem')

class GroupItem(models.Model):
    item = models.ForeignKey('Item', to_field = 'unique_field')
    group = models.ForeignKey('Group')
    title = models.CharField(max_length = 10)

and shell test sequence of commands:

Python 2.5.2 (r252:60911, Jan  4 2009, 17:40:26) 
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from testm2m.m2m.models import *
>>> item1 = Item.objects.create(unique_field = 100, title = 'Item')
>>> group1 = Group.objects.create(title = 'Group')
>>> membership = GroupItem.objects.create(item = item1, group = group1, title = 'Membership')
>>> membership.save()
>>> group1.members.all()
[]
>>> from django.db import connection
>>> connection.queries
[{'time': '0.002', 'sql': u'INSERT INTO "m2m_item" ("unique_field", "title") VALUES (100, Item)'}, {'time': '0.001', 'sql': u'INSERT INTO "m2m_group" ("title") VALUES (Group)'}, {'time': '0.001', 'sql': u'INSERT INTO "m2m_groupitem" ("item_id", "group_id", "title") VALUES (100, 1, Membership)'}, {'time': '0.000', 'sql': u'SELECT (1) AS "a", "m2m_groupitem"."id", "m2m_groupitem"."item_id", "m2m_groupitem"."group_id", "m2m_groupitem"."title" FROM "m2m_groupitem" WHERE "m2m_groupitem"."id" = 1  LIMIT 1'}, {'time': '0.001', 'sql': u'UPDATE "m2m_groupitem" SET "item_id" = 100, "group_id" = 1, "title" = Membership WHERE "m2m_groupitem"."id" = 1 '}, {'time': '0.000', 'sql': u'SELECT "m2m_item"."id", "m2m_item"."unique_field", "m2m_item"."title" FROM "m2m_item" INNER JOIN "m2m_groupitem" ON ("m2m_item"."id" = "m2m_groupitem"."item_id") WHERE "m2m_groupitem"."group_id" = 1  LIMIT 21'}]

PS: I've tried to make patch but couldn't find right place in django code :( I'll try tomorrow again.

[Ramiro Morales]

I see this in 1.1 too.

[Russell Keith-Magee]

Confirmed; exists in 1.1. On closer inspection, appears to be a duplicate of #11319. Thanks to Ramiro for doing the leg work.