CODEWITHSUNDEEP
pythonsqlsqlalchemy
user1711699
user1711699

Reputation: 141

SQLAlchemy - How to profit from dynamic and eager loading at the same time

I have two tables Factorys and Products, each Factory can have a large collection of Products, so the lazy=dynamic has been applied. 

class Factory(Base):
    __tablename__ = 'factorys'
    ID = Column(Integer, primary_key=True)
    products = relationship("Product",lazy='dynamic' )

class Product(Base):
    __tablename__ = 'products'
    ID = Column(Integer, primary_key=True)
    factory_id = Column(Integer, ForeignKey('factorys.ID'))
    Name = Column(Text)

In case all products of a factory are needed: 

factory.products.all()

should be applied. But since the factory is already loaded at this point of time, it is more performant to have an eagerjoined loading between Factory and Product.
But a joined relation between both tables make the overall performance worse due to the large collection of products, and is not required for example when appending products to a factory. Is it possible to define different relations between two tables, but using them only in specific cases? For example in a method for the factory class such as: 

class Factory(Base):
    __tablename__ = 'factorys'
    ID = Column(Integer, primary_key=True)
    products = relationship("Product",lazy='dynamic' )
    def _getProducts():
        return relationship("Product",lazy='joined' )

How can I get all the products of a factory in a performant way, not loosing performance when adding products to a factory? Any tips would be appreciated.

Upvotes: 3

Views: 866

Answers (1)

Vadman
Vadman

Reputation: 141

I have run into the same question and had a very difficult time finding the answer.

What you are proposing with returning a relationship will not work as SQLAlchemy must know about the relationship belonging to the table, but doing:

class Factory(Base):
__tablename__ = 'factorys'
ID = Column(Integer, primary_key=True)
products_dyn = relationship("Product",lazy='dynamic', viewonly=True)
products = relationship("Product",lazy='joined' )

should work. Note the viewonly attribute, it is very important because without it SQLAlchemy may try to use both relationships when you add a product to the factory and may produce duplicate entries in specific cases (such as using a secondary table for the relationship).

This way you could use both the eager loaded products and perform an optimized query with the join while hiding it with the table declaration.

Hope that helps!

Upvotes: 2

Related Questions