0

Say I have a query like so:

subquery = select(table1.c.id,
                  table1.c.type,
                  table1.c.some_category,
                  table2.c.some_other_category).join(table2,
                                                     table1.c.id== table2.c.id)

I want to use this query to perform another join on a third table. Like so:

# Fetch data
another_query = session.query(table3.c.id,
                              table3.c.aa,
                              table3.c.bb,
                              table3.c.cc,
                              table3.c.dd).subquery()

join = select(another_query.c.id,
              another_query.c.aa).join(subquery,
                                       another_query.c.id== subquery.c.id)

result = session.execute(join).fetchmany(1000)

I get the following error: Join target, typically a FROM expression, or ORM relationship attribute expected, got <sqlalchemy.sql.selectable.Select object.

How can I reuse the mentioned select subquery in the join statement?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ipsider
  • 553
  • 1
  • 7
  • 20
  • You should be able to do so using `alias()` when creating the `join` variable. Some info about this method can be found [here](https://stackoverflow.com/questions/5350033/usage-of-aliased-in-sqlalchemy-orm). – rochard4u Jul 19 '23 at 11:46

1 Answers1

1

You can try the below query:

subquery = select(
    table1.c.id,
    table1.c.type,
    table1.c.some_category,
    table2.c.some_other_category
).join(table2, table1.c.id == table2.c.id)

final_query = select(
    subquery.c.id,
    subquery.c.type,
    subquery.c.some_category,
    subquery.c.some_other_category,
    table3.c.column1,
    table3.c.column2
).join(table3, subquery.c.id == table3.c.id)

result = conn.execute(final_query)

It should join to your subquery.

Carmelot
  • 46
  • 2
  • That looks promising. The result is a ChunkedIteratorResult object right? How do I work with this result in order to get that result? – Ipsider Jul 19 '23 at 12:00
  • you can loop in it: `for row in result:` `id = row['id']` `type = row['type']` `some_category = row['some_category']` `some_other_category = row['some_other_category']` `column1 = row['column1']` `column2 = row['column2']` – Carmelot Jul 19 '23 at 12:06