sqlalchemy

joinedload and load_only but with filtering

I have two models with a simple FK relationship, Stock and Restriction (Restriction.stock_id FK to Stock).

class Restriction(Model):
    __tablename__ = "restrictions"
    id = db.Column(db.Integer, primary_key=True)
    stock_id = FK("stocks.id", nullable=True)
    name = db.Column(db.String(50), nullable=False)

class Stock(Model):
    __tablename__ = "stocks"
    id = db.Column(db.Integer, primary_key=True)
    ticker = db.Column(db.String(50), nullable=False, index=True)

I would like to retrieve Restriction object and related Stock but only Stock's ticker (there are other fields omitted here). I can simply do this with:

from sqlalchemy.orm import *

my_query = Restriction.query.options(
    joinedload(Restriction.stock).load_only(Stock.ticker)
)
r = my_query.first()

I get all columns for Restriction and only ticker for Stocks with above. I can see this in the SQL query run and also I can access r.stock.ticker and see no new queries run as it is loaded eagerly.

The problem is I cannot filter on stocks now, SQLAlchemy adds another FROM clause if I do my_query.filter(Stock.ticker == 'GME'). This means there is a cross product and it is not what I want.

On the other hand, I cannot load selected columns from relationship using join. ie.

Restriction.query.join(Restriction.stock).options(load_only(Restriction.stock))

does not seem to work with relationship property. How can I have a single filter and have it load selected columns from relationship table?

SQL I want run is:

 SELECT restrictions.*, stocks.ticker 
 FROM restrictions LEFT OUTER JOIN stocks ON stocks.id = restrictions.stock_id 
 WHERE stocks.ticker = 'GME'

And I'd like to get a Restriction object back with its stock and stock's ticker. Is this possible?

Solution

  • joinedload basically should not be used with filter. You probably need to take contains_eager option.

    from sqlalchemy.orm import *

my_query = Restriction.query.join(Restriction.stock).options(
    contains_eager(Restriction.stock).load_only(Stock.ticker)
).filter(Stock.ticker == 'GME')
r = my_query.first()

    Because you are joining using stock_id it will also be in the results as Stock.id beside Stock.ticker. But other fields would be omitted as you wish.

    I have written short post about it recently if you are interested: https://medium.com/@orzel.jarek/an-orm-can-bite-you-6ee9af8de19b