If you're using backrefs, it doesn't really matter which end of the relationship you configure. In the example above, it would be just as legitimate to remove the source_node and target_node relationship definitions, and define them on the NodesModel instead:
class NodesModel(Base): __tablename__ = "nodes" id = sa.Column(sa.Integer, primary_key=True) name = sa.Column(sa.String(50), nullable=False) targets = saorm.relationship( RelationshipsModel, foreign_keys=[RelationshipsModel.source_node_id], backref="source_node", ) sources = saorm.relationship( RelationshipsModel, foreign_keys=[RelationshipsModel.target_node_id], backref="target_node", ) The result is exactly the same. Another possibility is to define the relationships explicitly on *both* classes. If you do that, you would use "back_populates" instead of "backref" to tell SQLAlchemy that when you change the property on one class, the corresponding change must be made to the property on the other class. https://docs.sqlalchemy.org/en/13/orm/backref.html Simon On Wed, May 29, 2019 at 1:00 PM Desmond Lim <limwen...@gmail.com> wrote: > > 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. -- 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/CAFHwexf2qL4ih3n0SA4-p%3DcWd4tU-HoRDG5BDyTUEi3V8XPP0g%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.