Opened 7 years ago

Closed 7 years ago

Last modified 4 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 7 years ago by jan_oberst

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 7 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 7 years ago by casden@…

  • Owner changed from nobody to anonymous
  • Status changed from new to assigned

comment:4 Changed 7 years ago by anonymous

  • Owner changed from anonymous to cazzerson
  • Status changed from assigned to new

comment:5 Changed 7 years ago by jacob

  • milestone set to 1.1
  • Triage Stage changed from Unreviewed to Accepted

comment:6 Changed 7 years ago by Alex

  • Resolution set to invalid
  • Status changed from new to closed

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 4 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

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