Hi,
I'm a newbie integrating Django with a very large, legacy database. I have
the following models (showing what I believe to be relevant):
class StakeholderType(models.Model):
stakeholdertypeid = models.IntegerField(primary_key=True,
db_column='stakeholderTypeID')
typename = models.CharField(max_length=135, db_column='typeName',
blank=True)
....
class Stakeholder(models.Model):
stakeholderid = models.IntegerField(primary_key=True,
db_column='stakeholderID')
....
class Property(models.Model):
propertyid = models.IntegerField(primary_key=True,
db_column='propertyID')
rentablebuildingarea =
models.IntegerField(db_column='rentableBuildingArea')
propertymanagerid = models.ForeignKey(Stakeholder,
db_column='propertyManagerID', related_name="propertymanagerid_set")
ownerid = models.ForeignKey(Stakeholder, db_column='ownerID',
related_name="ownerid_set")
....
I build the following QuerySet:
self.results =
Stakeholder.objects.filter(Q(stakeholdertypeid__typename__exact='owner'))
self.results =
self.results.annotate(number_of_sqft=Sum('ownerid_set__rentablebuildingarea'))
self.results =
self.results.annotate(number_of_buildings=Count('ownerid_set'))
self.results = self.results.order_by('-number_of_sqft')
self.results = self.results[:form.cleaned_data['max_results']]
And I get the following SQL run against MySQL:
SELECT
`stakeholder`.`stakeholderID`,
...,
SUM(`property`.`rentableBuildingArea`) AS `number_of_sqft`,
COUNT(`property`.`propertyID`) AS `number_of_buildings`
FROM `stakeholder`
INNER JOIN `stakeholder_type` ON (`stakeholder`.`stakeholderTypeID` =
`stakeholder_type`.`stakeholderTypeID`)
LEFT OUTER JOIN `property` ON (`stakeholder`.`stakeholderID` =
`property`.`ownerID`)
WHERE `stakeholder_type`.`typeName` = 'owner' GROUP BY
`stakeholder`.`stakeholderID`
ORDER BY number_of_sqft DESC
LIMIT 33
The query performs twice as fast if I simply change the LEFT OUTER JOIN
generated by Django to an INNER JOIN. Does anyone have a recommendation for
how I might achieve an INNER JOIN of Stakeholder and Property? I've read
about Django doing this when it believes that the join could result in some
null rows. I've tried adding some "__isnull=False" specifications to my
QuerySet generation, but to no avail.
Any thoughts are appreciated. Thanks!
Joey
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
