Opened 7 years ago

Closed 6 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:

Description

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
        MATERIAL_CHOICES = ( [
                ( 'sh', _("Federstahl SH") ),
                ( 'r', _("Federstahl 1.4310") ),
        ])
        material = models.CharField(max_length=4, choices=MATERIAL_CHOICES, verbose_name=_(u"Material") )
        #artikelnumber
        articelnumber = models.CharField(max_length=16, unique=True, verbose_name=_(u"Artikelnummer") )
        #drahtduchrmesser
        wiresize = models.DecimalField(max_digits=10,decimal_places=5)
        #außendurchmesser
        outerdiameter = models.DecimalField(max_digits=10,decimal_places=5)
        #windungszahl
        windingcount = models.IntegerField(verbose_name=_(u"Gesamtwindungszahl"))
        #gewicht
        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)
        #federrate#
        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)
        #federrate
        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")
        #schängellänge1
        leglength1 = models.DecimalField(max_digits=10,decimal_places=5)
        #schänkellänge2
        leglength2 = models.DecimalField(max_digits=10,decimal_places=5)

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

BEGIN;
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
)
;
COMMIT;

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

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

comment:6 Changed 6 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