Join on a condition to eagerly load in sqlalchemy orm

Question

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()

Eager loading of the price for a stock:

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()

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 does not work here)).filter(Stock.stock_id == 1).one()

Answer 1

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