Join should be able to follow relationships implicitly

[Nick Retallack]

It's my opinion that this should work:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String, ForeignKeyConstraint
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship

Model = declarative_base()

class Parent(Model):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

class Child(Model):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    parent_id = Column(Integer, nullable=False)
    parent = relationship('Parent', primaryjoin='foreign(Child.parent_id) == remote(Parent.id)')

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)

if __name__ == "__main__":
    Model.metadata.create_all(engine)
    session = Session()

    # setup
    parent = Parent(name="fred")
    child = Child(name="bob", parent=parent)

    session.add_all([parent, child])
    session.commit()

    # payoff
    print session.query(Parent).join(Child).all()

It works if parent_id has a ForeignKeyConstraint. It also works if I change it to print session.query(Parent).join(Child.parent).all(). But I think it should work without either of these changes.

It would be very helpful in an application I'm working on now, which mixes queries very dynamically. I suppose I could work around it by searching my models for relationships to follow manually, or adding foreign key constraints to the model that do not actually exist in the database. I just wish it worked without having to do these things.

[Mike Bayer]

IMHO if you actually need this for your application to "work", vs. it being just a typing saver, you're doing it wrong. Your application should use inspect() to list out relationships, get the ones you want to join on and send them straight to join(). the query(A).join(B) joining just on foreign keys is another behavior that's been this way for a long time and I can't really change it without risking major backwards incompatible changes.

Because. If there is an FK relationship between Parent/Child, and a relationship (which is the overwhelming majority of cases):

1. we change the logic to only look for relationships, no longer foreign keys. Everyone's code that does this join() without foreign keys now has their code break.

2. we change the logic to look for foreign keys, or relationships. Now it is ambiguous which one to join on. We can:

a. favor the relationship over the FK. Now everyone's code where the relationship / FK is not exactly the same breaks.

b. favor the FK over the relationship. Now the feature doesn't really do what's advertised in a huge majority of cases. It is also dangerous. For example. In your code, suppose your primaryjoin is not as straightforward as it looks, one day some developer decides to add an innocuous foreign key to Child.parent_id. Suddenly, all your queries have changed to be not what you intended. Disaster.

c. try to figure out that the relationship and the FK refer to the same thing. Too complicated, definitely not happening anytime soon so again if you need this for your app right now, it isn't going to help you.

So what we see here is pretty much the wisdom of "explicit is better than implicit" at play. Unfortunately, relationship() and the FKs aren't necessarily the same and trying to guess (funny how the "refuse the temptation to guess" rule and the "explicit is better than implicit" so frequently play together) is just going to make things worse.

[Mike Bayer]

unfortunately, relationship() almost always is combined with the presence of a ForeignKey setup between the mapped tables, and including both will introduce an un-reconcilable ambiguity into the vast majority of cases and render the feature useless. the inspect() system has been provided for the case where users need to inspect the mappings of classes including relationship-related join points.