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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> wrote:
>> >>>
>> >>> On Wed, May 29, 2019 at 10:08 AM Desmond Lim <[email protected]> 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 [email protected].
>> >>> To post to this group, send email to [email protected].
>> >>> 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 [email protected].
>> > To post to this group, send email to [email protected].
>> > 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 [email protected].
>> To post to this group, send email to [email protected].
>> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
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.
