Transforming the expression of an aliased relationship

[Marnix le Noble]

Hello,

I have been banging my head against an issue for a couple weeks now and I was wondering if anyone was willing to help me out. I have tried looking in the SQLAlchemy documentation and previous topics but haven't found an answer as of yet. Imagine the following scenario:

# Tables
Base = declarative_base()

class Company(Base):
    __tablename__ = "company"
    id = Column(Integer, primary_key=True)


class Employee(Base):
    __tablename__ = "employee"
    id = Column(Integer, primary_key=True)
    first_company_id = Column(Integer, ForeignKey("company.id"))
    second_company_id = Column(Integer, ForeignKey("company.id"))

    first_company = relationship("Company", foreign_keys=first_company_id)
    second_company = relationship("Company", foreign_keys=second_company_id)


# Application code
child_alias = aliased(Company)
parent_alias = aliased(Employee)

query = session.query(parent_alias)
join = join(parent_alias, child_alias) # Throws multiple join paths exception (works fine if there is only one relationship to Company)
extra_on_clause = (1 == 1)

query = query.join(
    parent_alias,
    and_(join.onclause, extra_on_clause),
)

In my application we are trying to setup dynamic queries where users are able to dictate what relations are loaded in by a query. Because of this I don't know what tables and joins are going to be required and can't use the other version of joins since we want extra on_clauses:

# Can't use this because it doesn't allow extra clauses
query.join(parent_alias.first_company)

# Can't use this because I don't know the join expression (unknown part in bold)
query = query.join(
    parent_alias,
    and_(parent_alias.first_company_id == child_alias.id, extra_on_clause),
)

The things I was most hopeful about is the following:

# Almost produces the correct join clause except that the company.id is the wrong alias

onclause = parent_alias.first_company.expression # company.id = employee_1.first_company_id

So my question is can I transform this expression so that it uses the aliased(Company) instead and becomes 

company_1.id = employee_1.first_company_id

The only other solution I can think of is to check the private _user_defined_foreign_keys on the RelationshipProperty and piece together the join myself by checking the attributes etc. However this seems like the most hacky and least stable.

[Mike Bayer]

If you are able to use the relationship to generate the onclause, at some point you need to know both that you are using that relationship as part of your onclause, and that the target is going to be some target.  That's when the decision as to be made and you can do it using the ORM join construct, which will do the adaption for you:

from sqlalchemy.orm import join as orm_join

join = orm_join(parent_alias, child_alias, parent_alias.first_company)

print(join.onclause)

you can then use it in the query:

query = query.join(

    child_alias,

    and_(join.onclause, extra_on_clause),

)

the orm_join will only adapt the "onclause" if you pass it the relationship itself, not the underlying expression.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/90940889-2a85-4e6f-9c89-bb52d305b85d%40googlegroups.com.

[Marnix le Noble]

This is exactly what I was looking for! That's amazing, thank you very much. I wasn't aware you could pass a relationship to the onclause parameter of the join() function which I am now seeing is actually in the docs.

Cheers!

[Mike Bayer]

only the ORM version of it!    I'm glad it's useful because I was not sure if anyone actually uses that function anymore, but there you go.

On Thu, Apr 30, 2020, at 10:46 AM, Marnix le Noble wrote:

This is exactly what I was looking for! That's amazing, thank you very much. I wasn't aware you could pass a relationship to the onclause parameter of the join() function which I am now seeing is actually in the docs.

Cheers!

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f1981a14-27c7-4701-ba0d-17c7a0ea41ae%40googlegroups.com.