I have scoured the documentation looking for the preferred way of handling ambiguous joins, and it seems to be through use of aliased tables, but I still wonder if there's a better way. Intuitively, it seems like relationships hold enough information to make aliased tables unnecessary. But the API doesn't behave in that way (AFAIK), and I'm curious why.
Suppose I have the following the model: Base = declarative_base() class Parent(Base): __tablename__ = "parent" id = Column(Integer, primary_key=True) class Child(Base): __tablename__ = "child" id = Column(Integer, primary_key=True) mother_id = Column(Integer, ForeignKey("parent.id")) father_id = Column(Integer, ForeignKey("parent.id")) mother = relationship("Parent", foreign_keys=[mother_id]) father = relationship("Parent", foreign_keys=[father_id]) The Child table can join to the Parent table through 2 possible relationships: mother and father. When querying for Child entities, in order to reference columns through either of these relationships, it would appear that aliased() is required. Something like this: mother_alias = aliased(Parent) father_alias = aliased(Parent) statement = select(Child).join(mother_alias, Child.mother).join(father_alias, Child.father).where(mother_alias.id == "Martha", father_alias.id == "Bob") What I find strange about this is that the alias and relationship it's associated with are not inherently coupled together. I associate them with one another when I pass them together into the join method(), but this association is only implicit and not enforced. It's up to me to keep track of which alias belongs with which relationship. Given that I can join to a table using a relationship property, why is it not possible to also select columns and filter by columns using relationship properties? It seems like this would eliminate the need for aliased() in the above example entirely. This feels much more intuitive to me: statement = select(Child).join(Child.mother).join(Child.father).where(Child.mother.id == "Martha", Child.father.id == "Bob") In other words, the relationship can act as a proxy to its target table, and you can access all columns on that table through the relationship. The concept of an "aliased table" would be internal to the relationship, and the developer would not have to manage these aliases themselves. Is this possible? Is there an obvious reason I'm missing for why the API cannot function in this way? Thanks for any help or insight you can provide! -Dane -- 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/c9f82f00-bcbf-4a9b-aa33-8fce7ab63bbdn%40googlegroups.com.