0

I have 2 queries which both get a subset of a tables, let's call them Table1 and Table2
I would like to join these 2 child tables on their id.

I tried something like this, but it throws unhelpful errors:

table1: List[Table1] = db.session.execute(query1)
table2: List[Table2] = db.session.execute(query2)

db.session.query(table1).join(table2, table1.id == table2.id).all()
Ignace Vau
  • 532
  • 4
  • 19
  • 3
    You shouldn't execute the queries before joining. You just join the queries and that will execute the join. – Barmar Jun 17 '22 at 00:34
  • @Barmar I see, but then how do I specify that I want to join using the `id`? – Ignace Vau Jun 17 '22 at 00:38
  • You can't join raw queries like this. Remember, the join is implemented in the DB, not SQLAlchemy. If you're using raw queries, it doesn't know how to combine them. – Barmar Jun 17 '22 at 00:40
  • 1
    If you want to join the DB, how about just joining directly like this https://stackoverflow.com/a/6045131/10118118 ? – Yohan Jun 17 '22 at 00:53
  • Follow @Barmar's advice. But to _literally_ answer the original question, consider defining a pair of VIEWs, which some folks refer to as "named queries". Then it's very convenient to construct a JOIN of 1st view against 2nd view. – J_H Jun 17 '22 at 01:14

1 Answers1

2

You have to join the tables before executing the query. Maybe you wanted to do like this?

results = db.session.query(Table1).filter(
        {whatever the query1 does}, 
        {whatever the query2 does}
    ).join(Table2, Table1.id==Table2.id)
Nina Hwang
  • 66
  • 3
  • Thanks, combining the queries did the job. I also followed @J_H 's advice and used *view*s for conveniently storing my queries. – Ignace Vau Jun 17 '22 at 18:38