sqlalchemy and postgres: lateral expression without from clause

Question

I'm trying to do smth like this, but with sqlalchemy:

select t1.a, t2.x, l1.a_x, l2.a_x_y
  from t1
  join t2 on t1.id = t2.id
  join lateral (select t1.a + t2.x as a_x) l1 on true
  join lateral (select l1.a_x + t2.y as a_x_y) l2 on true

Problem is, sqlalchemy first autocorrelates all tables used in subquery, then, after using correlate_except, raises FROM clause expected exception.

UPDATE: here's a simplified demo: https://pastebin.com/XrkW2cMT sa.__version__: 1.3.17

My best guess is to use some FROM-imitation, smth like this:

select([]).select_from(text("(select 1) as X")).lateral("l1")

lateral (select a + b as c from (select 1) as X) l1

This works, but does not look pretty, and I'm concerned about possible performance issues in some weird edge cases.

Is there a better, cleaner way?

@IljaEverilä I never said any query failed, alchemy compiler refuses to compile select statement without from clause. — gistart, May 28 '20 at 16:59
@IljaEverilä sorry, I was working on the example. Pls chech the update — gistart, May 28 '20 at 17:30
Works with a little hand holding, i.e. add `.correlate(t1, t2)` to `l1` and `.correlate(l1, t2)` to `l2` (note that `correlate` and `correlate_except` take multiple arguments, not a list). Let's also edit this question so that the [mcve] is part of the question itself, not behind an external link :) — Ilja Everilä, May 28 '20 at 17:46
@IljaEverilä indeed it works this way, problem is this is an example query, my actual query has 4 tables and 6 laterals -- so even if I figure out the exact correlate order first time, this will be hard and very unintuitive to support and extend. I mean, maybe `from (select 1)` is a better hack in this case -- I'm looking for a way to make code simpler. Thanks for the help! — gistart, May 28 '20 at 17:54

sqlalchemy and postgres: lateral expression without from clause

0 Answers0