Incorrect SQL join construction

[Brian May]

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,))

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.

[Malcolm Tredinnick]

Fixed the description to prevent eye bleeding. We really a need a "preview" button people can use to check things ... oh, wait.. we have one! :-)