Opened 10 years ago

Closed 9 years ago

Last modified 7 years ago

#9132 closed (invalid)

QuerySet. count() doesn't do a Join if tables are inherited

Reported by: stsp1@… Owned by: cazzerson
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: count() QuerySet
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


the sql-query for result with correct joins:
SELECT "springshop_spring"."id", "springshop_spring"."material", "springshop_spring"."articelnumber", "springshop_spring"."wiresize", "springshop_spring"."outerdiameter", "springshop_spring"."windingcount", "springshop_spring"."weight", "springshop_pressurespring"."spring_ptr_id", "springshop_pressurespring"."freelengthLo", "springshop_pressurespring"."springconstant", "springshop_pressurespring"."blocklength" FROM "springshop_pressurespring" INNER JOIN "springshop_spring" ON ("springshop_pressurespring"."spring_ptr_id" = "springshop_spring"."id") WHERE wiresize=2

the sql-query for count with incorrect joins:
'SELECT COUNT(*) FROM "springshop_pressurespring" WHERE wiresize=2'

so the join is missing

Change History (7)

comment:1 Changed 10 years ago by jan_oberst

If you're counting the inherited model and the attribute wiresize is on that inherited model, there's maybe no need to do a join here and it was optimized away for a faster query.

Could you provide the model definitions and the ORM call that led to these SQL queries please?

comment:2 Changed 10 years ago by stsp1@…

==django model==

class Spring(models.Model):
        """This class represents a spring"""
        class Meta:
                verbose_name = _(u"Feder")
        MATERIAL_CHOICES = ( [
                ( 'sh', _("Federstahl SH") ),
                ( 'r', _("Federstahl 1.4310") ),
        material = models.CharField(max_length=4, choices=MATERIAL_CHOICES, verbose_name=_(u"Material") )
        articelnumber = models.CharField(max_length=16, unique=True, verbose_name=_(u"Artikelnummer") )
        wiresize = models.DecimalField(max_digits=10,decimal_places=5)
        outerdiameter = models.DecimalField(max_digits=10,decimal_places=5)
        windingcount = models.IntegerField(verbose_name=_(u"Gesamtwindungszahl"))
        weight = models.DecimalField(max_digits=10,decimal_places=5)

class PressureSpring(Spring):

        """This class represents a pressure spring"""
        class Meta:
                verbose_name = _(u"Druckfeder")
                verbose_name_plural = _(u"Druckfedern")

        #einhängelänge Lo
        freelengthLo = models.DecimalField(max_digits=10,decimal_places=5)
        springconstant = models.DecimalField(max_digits=10,decimal_places=5, verbose_name=_(u"Federrate") )
        blocklength = models.DecimalField(max_digits=10,decimal_places=5, verbose_name=_(u"Blocklänge") )

class PullSpring(Spring):
#class Zugfeder(Feder):
        """This class represents a pull-spring"""
        class Meta:
                verbose_name = _(u"Zugfeder")
                verbose_name_plural = _(u"Zugfedern")
        #Einhängelänge lo
        replacementlengthLo = models.DecimalField(max_digits=10,decimal_places=5)
        springconstant = models.DecimalField(max_digits=10,decimal_places=5)

class TorsionSpring(Spring):
        """This class represents a torsion spring"""
        class Meta:
                verbose_name = _(u"Torsionsfeder")
                verbose_name = _(u"Torsionsfedern")
        leglength1 = models.DecimalField(max_digits=10,decimal_places=5)
        leglength2 = models.DecimalField(max_digits=10,decimal_places=5)

==the sql code for creating these models ( output from python sqlall springshop )==

CREATE TABLE "springshop_spring" (
    "id" integer NOT NULL PRIMARY KEY,
    "material" varchar(4) NOT NULL,
    "articelnumber" varchar(16) NOT NULL UNIQUE,
    "wiresize" decimal NOT NULL,
    "outerdiameter" decimal NOT NULL,
    "windingcount" integer NOT NULL,
    "weight" decimal NOT NULL
CREATE TABLE "springshop_pressurespring" (
    "spring_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "springshop_spring" ("id"),
    "freelengthLo" decimal NOT NULL,
    "springconstant" decimal NOT NULL,
    "blocklength" decimal NOT NULL
CREATE TABLE "springshop_pullspring" (
    "spring_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "springshop_spring" ("id"),
    "replacementlengthLo" decimal NOT NULL,
    "springconstant" decimal NOT NULL
CREATE TABLE "springshop_torsionspring" (
    "spring_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "springshop_spring" ("id"),
    "leglength1" decimal NOT NULL,
    "leglength2" decimal NOT NULL

===python code===

result = models.PressureSpring.extra(where=['wiresize=%s'], params=[wiresize] )
func_count = result.count()
count = 0
for i in result:
     count = count + 1

after this code func_count != count and I think the problem is the missing join between the Spring-table and the PressureSpring-table

comment:3 Changed 10 years ago by casden@…

Owner: changed from nobody to anonymous
Status: newassigned

comment:4 Changed 10 years ago by anonymous

Owner: changed from anonymous to cazzerson
Status: assignednew

comment:5 Changed 9 years ago by Jacob

milestone: 1.1
Triage Stage: UnreviewedAccepted

comment:6 Changed 9 years ago by Alex Gaynor

Resolution: invalid
Status: newclosed

Your issue here is that you're using extra, and Django doesn't know the extra field is on a different table. This works fine if you use normal Django QuerySet operations.

comment:7 Changed 7 years ago by Jacob

milestone: 1.1

Milestone 1.1 deleted

Note: See TracTickets for help on using tickets.
Back to Top