How can I join two queries on a common relationship in sqlalchemy?

Question

Suppose I have two classes ChildA and ChildB which both have a relationship to Parent. Suppose also that I already have two queries defined, qa and qb, which query the respective child classes. I want to join qb onto qa upon having the same parent.

If parent were just an ordinary column, I could do the following:

subquery = qb.subquery()
joined_query = qa.join(subquery, AliasedChildA.parent==subquery.columns.parent)

Here, AliasedChildA is an alias of ChildA. (I have full control over how the queries are being created, but not over the filters that are being applied to them later.) But now, since parent is a relationship, it is not contained in subquery.columns.

How can I join qa and qb upon having the same parent?

van · Accepted Answer · 2012-11-26 10:35:07Z

1

Try the below:

qa = session.query(ChildA)
qb = session.query(ChildB)
# @note: *qa* and *qb* may include other filters etc, 
# but assuming they do not contain joins on *Parent*
subquery = qb.subquery()
qry = qa.join(Parent).join(subquery)

answered Nov 26, 2012 at 10:35

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Turion Over a year ago

That's a nice idea. The only problem I have is that I don't know in advance which class Parent the relationship parent is being mapped on. Maybe I should look into how to gather that information by introspection. (Maybe I should try to solve easier/more natural problems...)

van Over a year ago

As long as each ChildA/B has only one relationship to Parent, it should work. Otherwise, you should know which relationship you require for the purpose of your requirement: ..upon having the same parent

Tobu · Accepted Answer · 2012-11-24 13:23:45Z

1

Try it with AliasedChildA.parent_id == subquery.c.parent_id (and_ a few clauses if you have a composite pk).

answered Nov 24, 2012 at 13:23

Tobu

25.6k4 gold badges94 silver badges100 bronze badges

4 Comments

Turion Over a year ago

That's the workaround I'm currently trying. However, this relies on the relationship being built upon parent_id, and usually I can't control the names of the columns, only of the relationships. Can I somehow find out which column is used to establish the relationship?

Turion Over a year ago

Also, at the end I would like to perform a join upon something like parent1.children == parent2.children, and I have no idea about how to do that.

Tobu Over a year ago

You can do some introspection from orm classes (class.attribute.property.column), but you can't do it from your starting point of a subquery. Play around with an embedded IPython to see what you can introspect.

Alex Weavers Over a year ago

You can pass SQLAlchemy fields as arguments to your function directly. my_join_function(Model1.xxx, Model2.yyy) You can pass strings and use getattr(model, field_name) as well. I wouldn't recommend wildly introspecting things to find field names that match up. That seems like code smell.

weaming · Accepted Answer · 2020-07-29 07:18:12Z

1

query_a.subquery().join(query_b.subquery(), $on_condition...)

works for me :)

answered Jul 29, 2020 at 7:18

weaming

6,6831 gold badge28 silver badges16 bronze badges

3 Comments

egor83 Over a year ago

What's the format of $on_condition? Can you give an example, please?

weaming Over a year ago

@egor83 I forget it after so long time, sorry.

andyhasit Over a year ago

@egor83 something like query_a.c.col1 == query_b.c.col1

Collectives™ on Stack Overflow

How can I join two queries on a common relationship in sqlalchemy?

3 Answers 3

2 Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related