1

I must be a moron, because I cannot figure out how to ask SQLAlchemy to perform a simple, non-lazy inner join. In other words, return all the results in a single query.

The raw SQL query I would like to run is:

select
  city.population,
  state.name
from 
  city c inner join state s
    on c.state_id = s.id

The SQLAlchemy statement I'm using is:

session.query(City.population, State.name).all()

The City and State models already have the relationship defined, and the SQLAlchemy statement returns the correct values. But it takes forever, because it is doing individual "lazy" loads for the second value of every row in the recordset. The FROM statement is simply: FROM city, state

  • I have tried various configurations of options(joinedload(something here))
  • I have read "The Zen of Eager Loading," I find it unhelpful because it assumes the query is asking for entire model objects, not specific columns.
  • I read this answer which says joinedload is "meant to be entirely transparent" which, I can't agree with, because it seems to require a re-arrangement of the core query.
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
ExactaBox
  • 3,235
  • 16
  • 27
  • Entirely transparent at the ORM level in the sense that eager loads should not alter **the result of a `Query`** in any other way than load related objects. Of course the actual Core statement may differ. – Ilja Everilä Jul 27 '19 at 06:51

1 Answers1

1

I might be slightly off here, but have you tried explicitly passing the join condition?

q = session.query(City.population, State.name).join(State).all()

Also, assuming your objective is the initial query, have you tried a few tweaks in the sqlalchemy syntax to actually get the same statement?

print (q.statement)

Lastly, Query classes have a method enable_eagerloads(). From the docs:

Control whether or not eager joins and subqueries are rendered.

When set to False, the returned Query will not render eager joins regardless of joinedload(), subqueryload() options or mapper-level lazy='joined'/lazy='subquery' configurations.

realr
  • 3,652
  • 6
  • 23
  • 34
  • 1
    Yes I had previewed the constructed statements (via echo=True) and had tried `enable_eagerloads()`, plus `joinedload`, and much more from https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#joined-eager-loading . Specifically: "while Query.join() is used to alter the results of a query, joinedload() goes through great lengths to not alter the results of the query". In fact, adding `query.join()` did not change the results, just the speed at which those results were returned -- thus it seems this quote is either misleading or inaccurate. Thanks for the `query.join()` suggestion. – ExactaBox Jul 27 '19 at 02:37
  • I have struggled with some of the documentation from `sqlalchemy`, even understanding some of the basic classes behavior. Glad it helped a bit. – realr Jul 27 '19 at 02:40
  • I have a great fondness for SQL and purposely avoided ORM's for a long time. I find SQLAlchemy to be like taking a Formula 1 car and driving it through a parking lot filled with speed bumps. Its documentation is a large part of that. Oh well. Thanks again. – ExactaBox Jul 27 '19 at 02:47
  • 1
    @exactabox The quoted part is exact. Eager loading is an ORM concept for filling relationships, while `Query.join()` is used for just that, an explicit join (for filtering, or what have you — the usual SQL stuff). – Ilja Everilä Jul 27 '19 at 07:12