id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
13343	Wrong behavior of ManyToMany relationship while using 'trough' in ManyToManyField  and 'db_column' and 'to_field' properties in intermediate model	Alexander Kaidalov	nobody	"= Models =
{{{
#!python
# 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) =
{{{
#!text

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 ==
{{{
#!sql
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."		closed	Database layer (models, ORM)	1.2-beta		duplicate			Accepted	0	0	0	0	0	0