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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c9f82f00-bcbf-4a9b-aa33-8fce7ab63bbdn%40googlegroups.com.
