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
10028	Incorrect SQL join construction	Brian May	Malcolm Tredinnick	"Continued from <http://groups.google.com/group/django-users/browse_thread/thread/bc3ecc16c3def53b>
{{{
Python 2.5.2 (r252:60911, Oct  5 2008, 19:24:49) 
[GCC 4.3.2] on linux2
Type ""help"", ""copyright"", ""credits"" or ""license"" for more information.
(InteractiveConsole)
>>> import inventory.models as models
>>> object=models.software.objects.get(id=267)
>>> print models.software_installation.objects.filter(software=object).count()
10
>>> print models.software_installation.objects.filter(software=object)
[]
>>> print models.software_installation.objects.filter(software=object).query.as_sql()
('SELECT `inventory_software_installation`.`id`, 
`inventory_software_installation`.`software_id`, 
`inventory_software_installation`.`license_key_id` FROM 
`inventory_software_installation` INNER JOIN `inventory_software` 
ON (`inventory_software_installation`.`software_id` = 
`inventory_software`.`id`) LEFT OUTER JOIN `inventory_license_key` 
ON (`inventory_software_installation`.`license_key_id` = 
`inventory_license_key`.`id`) INNER JOIN `inventory_software` T4 ON 
(`inventory_license_key`.`software_id` = T4.`id`) INNER JOIN 
`inventory_license` ON (`inventory_license_key`.`license_id` = 
`inventory_license`.`id`) INNER JOIN `inventory_software` T6 ON 
(`inventory_license`.`software_id` = T6.`id`) WHERE 
`inventory_software_installation`.`software_id` = %s  ORDER BY 
`inventory_software`.`name` ASC, T4.`name` ASC, T6.`name` ASC, 
`inventory_license`.`vendor_id` ASC, `inventory_license_key`.`key` 
ASC', (267,))

}}}

{{{
#!python
class software(models.Model):
    name = models.CharField(max_length=100)
    class Meta:
        ordering = ('name',)

class license(models.Model):
    software = models.ForeignKey(software)
    vendor_id = models.CharField(max_length=10,null=True,blank=True)
    class Meta:
        ordering = ('software','vendor_id')

class license_key(models.Model):
    software = models.ForeignKey(software)
    license  = models.ForeignKey(license)
    key = models.CharField(max_length=50,null=True,blank=True)
    class Meta:
        ordering = ('software','license','key')

class software_installation(models.Model):
    software = models.ForeignKey(software)
    license_key = models.ForeignKey(license_key,null=True,blank=True)
    class Meta:
        ordering = ('software','license_key')
}}}

The ordering Meta tag is important; if I remove the appropriate meta tags (I haven't worked out which ones yet), then everything works fine.

Brian May

PS Thanks for the very prompt response."		closed	Database layer (models, ORM)	1.0		fixed			Accepted	0	0	0	0	0	0