foreign_keys and backref are different concepts. foreign_keys is a hint to SQLAlchemy on how to create the join condition between 2 classes. backref specifies a property that should be created on the other end of the relationship to allow you to follow the relationship in the other direction.
For example, if you had this: import sqlalchemy as sa import sqlalchemy.orm as saorm from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class RelationshipsModel(Base): __tablename__ = "relationships" source_node_id = sa.Column( sa.Integer, sa.ForeignKey("nodes.id"), primary_key=True ) target_node_id = sa.Column( sa.Integer, sa.ForeignKey("nodes.id"), primary_key=True ) strength = sa.Column(sa.Integer, nullable=False) source_node = saorm.relationship( "NodesModel", foreign_keys=[source_node_id], backref="targets", ) target_node = saorm.relationship( "NodesModel", foreign_keys=[target_node_id], backref="sources", ) class NodesModel(Base): __tablename__ = "nodes" id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(50), nullable=False) if __name__ == "__main__": engine = sa.create_engine("sqlite://", echo="debug") Base.metadata.create_all(bind=engine) session = saorm.Session(bind=engine) node1 = NodesModel(name="node1") node2 = NodesModel(name="node2") relationship = RelationshipsModel( source_node=node1, target_node=node2, strength=10 ) session.add_all([node1, node2, relationship]) session.flush() print(node1.targets) Given a node, you can access the relationships which use that node as a source via the backref "node.targets", and the relationships that use that node as a target via "node.sources". Hope that helps, Simon On Wed, May 29, 2019 at 11:49 AM Desmond Lim <limwen...@gmail.com> wrote: > > Hi there, > > Sorry, I've actually found the solution after I've posted my question again. > > But I have to ask. > > I'm doing this in my relationships model: > > source_node = relationship("NodesModel", foreign_keys=[source_node_id]) > target_node = relationship("NodesModel", foreign_keys=[target_node_id]) > > And removed this from the nodes model: > > relationships = relationship("RelationshipsModel", backref="nodes") > > My questions are: > > The backref allows the linking of the 2 tables bidirectionally. Does the > foreign_keys in the relationships model do the same thing? > Why do we not use the foreign_keys method to link all the tables instead of > backref in the parent table? > > Thanks. > Desmond > > > On Wed, 29 May 2019 at 18:38, Desmond Lim <limwen...@gmail.com> wrote: >> >> Hi Simon, >> >> I've read and I've tried a number of what is written but I still can't solve >> it. >> >> I've done this: >> >> class RelationshipsModel(db.Model): >> __tablename__ = 'relationships' >> >> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> strength = db.Column(db.Integer, nullable=False) >> >> source_node = relationship("NodesModel") >> target_node = relationship("NodesModel") >> >> class NodesModel(db.Model): >> __tablename__ = 'nodes' >> >> id = db.Column(db.BigInteger, primary_key=True) >> project_uuid = db.Column(UUID(as_uuid=True), >> db.ForeignKey('projects.uuid')) >> name = db.Column(db.String(50), nullable=False) >> size = db.Column(db.Integer, nullable=False) >> >> posts_nodes = relationship("PostsNodesModel", backref="nodes") >> >> Below are all that I"ve tried. >> >> ----- >> >> class RelationshipsModel(db.Model): >> __tablename__ = 'relationships' >> >> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> strength = db.Column(db.Integer, nullable=False) >> >> source_node = relationship("NodesModel") >> target_node = relationship("NodesModel") >> >> class NodesModel(db.Model): >> __tablename__ = 'nodes' >> >> id = db.Column(db.BigInteger, primary_key=True) >> project_uuid = db.Column(UUID(as_uuid=True), >> db.ForeignKey('projects.uuid')) >> name = db.Column(db.String(50), nullable=False) >> size = db.Column(db.Integer, nullable=False) >> >> posts_nodes = relationship("PostsNodesModel", backref="nodes") >> relationships_s = relationship("RelationshipsModel", >> foreign_keys=["relationships.source_node_id"], backref="nodes") >> relationships_t = relationship("RelationshipsModel", >> foreign_keys=["relationships.target_node_id"], backref="nodes") >> >> ----- >> >> class RelationshipsModel(db.Model): >> __tablename__ = 'relationships' >> >> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> strength = db.Column(db.Integer, nullable=False) >> >> class NodesModel(db.Model): >> __tablename__ = 'nodes' >> >> id = db.Column(db.BigInteger, primary_key=True) >> project_uuid = db.Column(UUID(as_uuid=True), >> db.ForeignKey('projects.uuid')) >> name = db.Column(db.String(50), nullable=False) >> size = db.Column(db.Integer, nullable=False) >> >> posts_nodes = relationship("PostsNodesModel", backref="nodes") >> relationships_s = relationship("RelationshipsModel", >> foreign_keys=["relationships.source_node_id"], backref="nodes") >> relationships_t = relationship("RelationshipsModel", >> foreign_keys=["relationships.target_node_id"], backref="nodes") >> >> ----- >> >> class RelationshipsModel(db.Model): >> __tablename__ = 'relationships' >> >> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >> primary_key=True) >> strength = db.Column(db.Integer, nullable=False) >> >> class NodesModel(db.Model): >> __tablename__ = 'nodes' >> >> id = db.Column(db.BigInteger, primary_key=True) >> project_uuid = db.Column(UUID(as_uuid=True), >> db.ForeignKey('projects.uuid')) >> name = db.Column(db.String(50), nullable=False) >> size = db.Column(db.Integer, nullable=False) >> >> posts_nodes = relationship("PostsNodesModel", backref="nodes") >> relationships_s = relationship("RelationshipsModel", >> foreign_keys=["relationships.source_node_id"], backref="nodes") >> relationships_t = relationship("RelationshipsModel", >> foreign_keys=["relationships.target_node_id"], backref="nodes") >> >> I've also tried using >> >> relationships = relationship("RelationshipsModel", >> foreign_keys="[NodesModel.source_node_id, >> NodesModel.target_node_id]", >> backref="nodes") >> >> Any other codes that I haven't tried? >> >> Desmond >> >> On Wed, 29 May 2019 at 17:30, Simon King <si...@simonking.org.uk> wrote: >>> >>> On Wed, May 29, 2019 at 10:08 AM Desmond Lim <limwen...@gmail.com> wrote: >>> > >>> > Hi there, >>> > >>> > I'm been puzzling over this and still can't find answer. >>> > >>> > I have 2 tables: >>> > >>> > Nodes: >>> > >>> > class NodesModel(db.Model): >>> > __tablename__ = 'nodes' >>> > >>> > id = db.Column(db.BigInteger, primary_key=True) >>> > project_uuid = db.Column(UUID(as_uuid=True), >>> > db.ForeignKey('projects.uuid')) >>> > name = db.Column(db.String(50), nullable=False) >>> > size = db.Column(db.Integer, nullable=False) >>> > >>> > posts_nodes = relationship("PostsNodesModel", backref="nodes") >>> > relationships = relationship("RelationshipsModel", backref="nodes") >>> > >>> > Relationships: >>> > >>> > class RelationshipsModel(db.Model): >>> > __tablename__ = 'relationships' >>> > >>> > source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >>> > primary_key=True) >>> > target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'), >>> > primary_key=True) >>> > strength = db.Column(db.Integer, nullable=False) >>> > >>> > I'm getting errors on this line: >>> > >>> > relationships = relationship("RelationshipsModel", backref="nodes") >>> > >>> > And I know it is because my Relationships table has the Nodes table as a >>> > foreign key twice. But I have not idea how do I create 2 relationships to >>> > the Relationships table? >>> > >>> >>> I assume the error you are getting is something like "Could not >>> determine join condition..."? In which case, you probably need this >>> section of the docs: >>> >>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#handling-multiple-join-paths >>> >>> Hope that helps, >>> >>> Simon >>> >>> -- >>> 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 post to this group, send email to sqlalchemy@googlegroups.com. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexczSoXe-GCrfDB%2BD6tisADXkz1EBqtjhyMz2La58tL7yw%40mail.gmail.com. >>> For more options, visit https://groups.google.com/d/optout. > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzagzkvG2fwY3VtZNQR_6gBQsUFvmxkr7pBDJAZmgpY1Feg%40mail.gmail.com. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfuvzX3hXAu6dbKCRM%2BhQdKMt2d9c7hLecy2UOu1uUEgg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.