CODEWITHSUNDEEP
sqlalchemy
Luca Fiaschi
Luca Fiaschi

Reputation: 3205

Sqlalchemy eager loading of parent all properties in joined table inheritance

I have the following problem:

I have a hierachy of classes with joined table inheritance:

class AdGroupModel(Base, AdwordsRequestMixin):
    __tablename__ = 'ad_groups'

    db_id = Column(BigInteger, primary_key=True)
    created_at = Column(DateTime(timezone=False), nullable=False, default=datetime.datetime.now())

    # ----RELATIONS-----
    # campaign MANY-to-ONE
    campaign_db_id = Column(BigInteger,
                            ForeignKey('campaigns.db_id', ondelete='CASCADE'),
                            nullable = True,
                            )
    # # ads ONE-to-MANY
    ads = relationship("AdModel",
                       backref="ad_group",
                       lazy="subquery",
                       passive_deletes=True,
                       single_parent=True,
                       cascade="all, delete, delete-orphan")
    # # # keywords ONE-to-MANY
    criteria = relationship("AdGroupCriterionModel",
                            backref="ad_group",
                            lazy="subquery",
                            passive_deletes=True,
                            single_parent=True,
                            cascade="all, delete, delete-orphan")


    # Joined Table Inheritance
    type = Column(Unicode(50))
    __mapper_args__ = {
        'polymorphic_identity': 'ad_group',
        'polymorphic_on': type
    }





class AdGroupCriterionModel(Base, AdGroupDependenceMixin):
    __tablename__ = 'ad_group_criterion'

    db_id = Column(BigInteger, primary_key=True)

    destination_url = Column(Unicode, nullable=True)
    status = Column(Enum("PAUSED", "ACTIVE", "DELETED",
                         name='criterion_status'), default="ACTIVE")



    # ----RELATIONS---
    # ad_group ONE-to-MANY
    ad_group_db_id = Column(BigInteger, ForeignKey('ad_groups.db_id',
                                                   ondelete='CASCADE'), nullable=True)

    # Joined Table Inheritance
    criterion_sub_type = Column(Unicode(50))
    __mapper_args__ = {
        'polymorphic_on': criterion_sub_type
    }



class AdGroupKeywordModel(AdGroupCriterionModel):
    __tablename__ = 'ad_group_keyword'
    __mapper_args__ = {'polymorphic_identity': 'Keyword'}
    db_id = Column(Integer, ForeignKey('ad_group_criterion.db_id'), primary_key=True)

    text = Column(Unicode, nullable=False)


class AdGroupDependenceMixin(object):
    _aggad_id = Column(BigInteger, nullable=True)
    _agname = Column(Unicode, nullable=True)

    @hybrid_property
    def ad_group_GAD_id(self):
        if self.ad_group is None:
            res = self._aggad_id
        else:
            res = self.ad_group.GAD_id
        return res

    @ad_group_GAD_id.setter
    def ad_group_GAD_id(self, value):
        self._aggad_id = value
        if value is not None:
            self.ad_group = None

    @ad_group_GAD_id.expression
    def ad_group_GAD_id(cls):
        what = case([( cls._aggad_id != None,  cls._aggad_id)], else_=AdGroupModel.GAD_id)
        return what.label('adgroupgadid_expression')

    @hybrid_property
    def ad_group_name(self):
        if self.ad_group is None:
            return self._agname
        else:
            return self.ad_group.name

    @ad_group_name.setter
    def ad_group_name(self, value):
        self._agname = value
        if value is not None:
            self.campaign = None

    @ad_group_name.expression
    def ad_group_name(cls):
        what = case([( cls._agname != None,  cls._agname)], else_=AdGroupModel.name)
        return what.label('adgroupname_expression')




And I load the Keywords objects from the database with the following query:

    all_objects1 = self.database.session.query(AdGroupKeywordModel).join(AdGroupModel)\
        .options(subqueryload('ad_group'))\
        .filter(AdGroupModel.GAD_id!=None)\
        .limit(self.options.limit).all()

which returns obejcts of type AdGroupKeywordModel.

Unfortunately every time I try to access the properties of the AdGroupKeywordModel which are in the parent table (AdGroupCriterionModel) a query of this type is emitted:

sqlalchemy.engine.base.Engine 
    SELECT ad_group_criterion.destination_url AS ad_group_criterion_destination_url, ad_group_criterion.status AS ad_group_criterion_status, ad_group_criterion.ad_group_db_id AS ad_group_criterion_ad_group_db_id, ad_group_criterion.criterion_sub_type AS ad_group_criterion_criterion_sub_type, ad_group_keyword.text AS ad_group_keyword_text
    FROM ad_group_criterion JOIN ad_group_keyword ON ad_group_criterion.db_id = ad_group_keyword.db_id

which is strongly compromising the performace.

What I would like to have is that all the attributes for the class AdGroupKeywordModel which are related to the parent (and other classes defined in the relationship) to be loaded with the initial query and be cached for further use. So that when I access them I do not get any overhead from further sqlstatements.

It seems that eager loading is only defined for relationships but not for hierarchies. Is it possible to have this behaviour in sqlalchemy for hierarchies as well?

Thanks

Upvotes: 0

Views: 1362

Answers (1)

knitti
knitti

Reputation: 7033

What I see is: only AdGroupModel has a relationship with a lazy= definition (which is the keyword which defines eager loading for relationships), and the query only has a subqueryload('ad_group').

The only point, in which ad_group or AdGroupModel touch with AdGroupKeywordModel is in AdGroupModel.criteria, which has as backref AdGroupCriterionModel.ad_group. I'm not familiar with the subqueryload syntax, but If I would want to eager-load AdGroupCriterionModel.ad_group, I'd define criteria like this:

criteria = relationship(
    "AdGroupCriterionModel", backref=backref("ad_group", lazy="subquery"),
    lazy="subquery", passive_deletes=True, single_parent=True,
    cascade="all, delete, delete-orphan")

The key would be in defining the right lazy also for the backref.

Upvotes: 2

Related Questions