Guest User

sqlalchemy lateral question

a guest
May 28th, 2020
168
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 2.28 KB | None | 0 0
  1. import sqlalchemy as sa
  2.  
  3. #
  4. ## setup
  5. #
  6.  
  7. t1 = sa.table(
  8.     "t1",
  9.     sa.column("id", sa.Integer),
  10.     sa.column("a", sa.Integer),
  11.     sa.column("b", sa.Integer)
  12. )
  13.  
  14. t2 = sa.table(
  15.     "t2",
  16.     sa.column("id", sa.Integer),
  17.     sa.column("x", sa.Integer),
  18.     sa.column("y", sa.Integer)
  19. )
  20.  
  21. #
  22. ## all auto
  23. #
  24.  
  25. l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).lateral("l1")
  26. l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).lateral("l2")
  27.  
  28. join = t1.join(t2, t1.c.id == t2.c.id)\
  29.     .join(l1, sa.true())\
  30.     .join(l2, sa.true())
  31.  
  32. query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
  33. print(str(query))
  34.  
  35. """
  36. sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT t1.a + t2.x AS a_x
  37. FROM t1, t2' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.
  38. """
  39.  
  40.  
  41. #
  42. ## correlate(None) -> autocorrelation
  43. #
  44.  
  45. l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).correlate(None).lateral("l1")
  46. l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).correlate(None).lateral("l2")
  47.  
  48. join = t1.join(t2, t1.c.id == t2.c.id)\
  49.     .join(l1, sa.true())\
  50.     .join(l2, sa.true())
  51.  
  52. query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
  53.  
  54. """
  55. SELECT t1.a, t2.x, l1.a_x, l2.a_x_y
  56. FROM t1 JOIN t2 ON t1.id = t2.id JOIN LATERAL (SELECT t1.a + t2.x AS a_x
  57. FROM t1, t2) AS l1 ON true JOIN LATERAL (SELECT l1.a_x + t2.y AS a_x_y
  58. FROM LATERAL (SELECT t1.a + t2.x AS a_x
  59. FROM t1, t2) AS l1, t2) AS l2 ON true
  60. """
  61.  
  62. #
  63. ## correlate_except
  64. #
  65.  
  66. l1 = sa.select([ (t1.c.a + t2.c.x).label("a_x") ]).correlate_except([t1, t2]).lateral("l1")
  67. l2 = sa.select([ (l1.c.a_x + t2.c.y).label("a_x_y") ]).correlate_except([t1, t2]).lateral("l2")
  68.  
  69. join = t1.join(t2, t1.c.id == t2.c.id)\
  70.     .join(l1, sa.true())\
  71.     .join(l2, sa.true())
  72.  
  73. query = sa.select([ t1.c.a, t2.c.x, l1.c.a_x, l2.c.a_x_y ]).select_from(join)
  74.  
  75. """
  76. sqlalchemy.exc.ArgumentError: FROM expression expected
  77. """
  78.  
  79.  
  80. #
  81. ## proof, postgres 10+
  82. #
  83.  
  84. """
  85. create table t1 ( id int, a int, b int);
  86. create table t2 (id int, x int, y int);
  87.  
  88. select t1.a, t2.x, l1.a_x, l2.a_x_y
  89.  from t1
  90.  join t2 on t1.id = t2.id
  91.  join lateral (select t1.a + t2.x as a_x) l1 on true
  92.  join lateral (select l1.a_x + t2.y as a_x_y) l2 on true
  93.  
  94. """
Add Comment
Please, Sign In to add comment