Skip to content

Instantly share code, notes, and snippets.

@nickretallack
Last active May 21, 2023 22:18
Show Gist options
  • Save nickretallack/7cf6d4f255b248a9f6ec to your computer and use it in GitHub Desktop.
Save nickretallack/7cf6d4f255b248a9f6ec to your computer and use it in GitHub Desktop.
How do I do a join without a real foreign key constraint?
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()
2014-11-20 17:24:21,129 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-11-20 17:24:21,129 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,130 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-11-20 17:24:21,130 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,130 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("parent")
2014-11-20 17:24:21,131 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,131 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("child")
2014-11-20 17:24:21,131 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,131 INFO sqlalchemy.engine.base.Engine
CREATE TABLE parent (
id INTEGER NOT NULL,
name VARCHAR NOT NULL,
PRIMARY KEY (id)
)
2014-11-20 17:24:21,131 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,132 INFO sqlalchemy.engine.base.Engine COMMIT
2014-11-20 17:24:21,132 INFO sqlalchemy.engine.base.Engine
CREATE TABLE child (
id INTEGER NOT NULL,
name VARCHAR NOT NULL,
parent_id INTEGER NOT NULL,
PRIMARY KEY (id)
)
2014-11-20 17:24:21,132 INFO sqlalchemy.engine.base.Engine ()
2014-11-20 17:24:21,132 INFO sqlalchemy.engine.base.Engine COMMIT
Traceback (most recent call last):
File "joins.py", line 35, in <module>
print session.query(Parent).join(Child).all()
File "/Users/nickretallack/virtualenvs/newflask/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1724, in join
from_joinpoint=from_joinpoint)
File "<string>", line 2, in _join
File "/Users/nickretallack/virtualenvs/newflask/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 191, in generate
fn(self, *args[1:], **kw)
File "/Users/nickretallack/virtualenvs/newflask/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1858, in _join
outerjoin, create_aliases, prop)
File "/Users/nickretallack/virtualenvs/newflask/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1928, in _join_left_to_right
self._join_to_left(l_info, left, right, onclause, outerjoin)
File "/Users/nickretallack/virtualenvs/newflask/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2056, in _join_to_left
"Tried joining to %s, but got: %s" % (right, ae))
sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class '__main__.Child'>, but got: Can't find any foreign key relationships between 'parent' and 'child'.
@xuyang2
Copy link

xuyang2 commented Nov 19, 2015

parent = relationship('Parent',
                      backref=backref('children'),
                      primaryjoin='foreign(Child.parent_id) == remote(Parent.id)')

@dandanmylady
Copy link

@xuyang2 , thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment