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.
On Thu, Apr 30, 2020, at 4:48 AM, Marnix le Noble wrote:
> 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.
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/90940889-2a85-4e6f-9c89-bb52d305b85d%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/90940889-2a85-4e6f-9c89-bb52d305b85d%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/ee17eb87-ce1d-4781-9ac0-2400db906719%40www.fastmail.com.
