Hi Simon, Thanks for the help just a follow up to clarify this.
Does this mean if I place the backref in the relationships definition in the Relationships model, this works the same way as the backref in a parent model? I'm asking because backref have all been placed in the parent models and for this it is in the child model. Or is my understanding off? Desmond On Wed, 29 May 2019 at 19:21, Simon King <si...@simonking.org.uk> wrote: > 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. > -- 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%2BCzaj9h%3DZCoeVL9aL%2BPP66xKM8GjWBE%2BUVRW2OcrEoCPyKKg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.