加入条件急切加载 sqlalchemy orm [英] Join on a condition to eagerly load in sqlalchemy orm

问题描述

from sqlalchemy.orm import subqueryload, joinedload, eagerload
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func,Float, sql
from sqlalchemy.orm import relation
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('sqlite:///testdb.sqlite')
engine.echo = True
Base = declarative_base()
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)
s= session()

class Stock(Base):
__tablename__ = 'stock'
stock_id = Column(Integer, primary_key=True)
name = Column(String)
prices = relation("StockPrice")

class StockPrice(Base):
__tablename__ = 'stock_price'
stock_id = Column(Integer, ForeignKey('stock.stock_id'), primary_key=True)
date = Column(String, primary_key=True)
price = Column(Float)
source = Column(String, primary_key=True)
user = Column(String)

Base.metadata.create_all(engine)

stockprice1 = StockPrice(stock_id = 1, date="2014-10-29", price="170.0", source="X Firm", user="U1")
stockprice2 = StockPrice(stock_id = 1, date="2014-10-30", price="175.0", source="X Firm", user="U2")
stock1 = Stock(stock_id = 1, name = "GOOGLE", prices=[stockprice1, stockprice2])

stockprice1 = StockPrice(stock_id = 2, date="2014-10-29", price="150.0", source="X Firm", user="U1")
stockprice2 = StockPrice(stock_id = 2, date="2014-10-30", price="155.0", source="X Firm", user="U2")
stock2 = Stock(stock_id = 2, name = "YAHOO", prices=[stockprice1, stockprice2])

s.add_all([stock1, stock2])
s.commit()

急切加载股票价格:

stock = s.query(Stock).options(joinedload(Stock.prices)).filter(Stock.stock_id == 1).one()

急切加载给定日期的股票价格的一种方法:

One way to eagerly load price for a stock for a given date:

stock = s.query(Stock).options(joinedload(Stock.prices)).filter(Stock.stock_id == 1).filter(StockPrice.date == "2014-10-30").one()

但是这种方法的问题是，如果您有可能像 StockPrice 这样与 Stock 相关的表，并且如果您想加载给定日期的所有关系，那么在加入所有关系后，结果集会变得非常庞大.过滤器在 WHERE 子句中添加条件，而不是我需要一种方法来指定加入条件以急切加载.

But the problem with the approach is if you have may tables like StockPrice related to Stock and if you want to load the all relations for a given date then the result set becomes very huge after joining all relations. Filter adds the conditions in WHERE clause, instead I need a way to specify condition on join to eagerly load.

stock = s.query(Stock).options(joinedload(Stock.prices, #condition 在这里不起作用)).filter(Stock.stock_id == 1).one()

推荐答案

代替 joinedload(Stock.prices) 执行以下操作:

Instead of joinedload(Stock.prices) do the following:

stock = (s.query(Stock)

         # @note: this replaces `joinedload(Stock.prices)`
         .join(StockPrice,
               and_(StockPrice.stock_id == Stock.stock_id,
                    StockPrice.date == "2014-10-30")
               )

         # effectively *trick* SQLAlchemy into thinking that above we loaded all
         # items for the relationship *Stock.prices*
         .options(contains_eager(Stock.prices))

         ).get(1) # will retrieve the instance for Stock.stock_id = 1

这篇关于加入条件急切加载 sqlalchemy orm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！