Re: [sqlalchemy] Child table with 2 relationships to parent
I think everything you have said is correct. If you use "relationship"
to create a link from class A to class B, then "backref" is just a
shortcut to create a corresponding relationship in the other
direction, from B to A. They are entirely optional; you don't have to
create a backref if you don't want to.
Since backrefs are created as properties on the target class, the name
of the backref cannot be the same as any other property on the target
class. Other than that restriction, you can name the backref whatever
you like.
Simon
On Thu, May 30, 2019 at 2:09 AM Desmond Lim wrote:
>
> Thanks Simon for your help. I have been playing around with the backref and I
> think I understand it, I just want to clarify my understanding before "close"
> this question.
>
> There is no requirements to set a backref to any table, it could be the main
> table or the table that contains the main table as a foreign key.
> The backref variable (e.g. backref="nodes") is just a name and it doesn't
> refer to a table or model.
> The backref is the "reference" with which the main table data is obtained by
> the model with the foreign key (as per your example given). That is to say,
> if the backref of AModel is b, I get AModel by object.b.
> The naming of the relationship (i.e. nodes = relationship(), where the
> nodes is the name), is just that a name and if names are duplicated in
> different models, it doesn't matter.
> A relationship defined by a backref has to be unique.
>
> nodes = relationship("NodesModel", backref="nodes)
>
>
> in a model, I cannot have the same relationship in another model as the
> relationship has to be unique if pointing to the same model, so something like
>
> nodes = relationship("EdgesModel", backref="nodes")
>
>
> would work.
>
> Desmond
>
> On Wed, 29 May 2019 at 20:10, Simon King wrote:
>>
>> 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 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 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 =
Re: [sqlalchemy] Child table with 2 relationships to parent
Thanks Simon for your help. I have been playing around with the backref and
I think I understand it, I just want to clarify my understanding before
"close" this question.
1. There is no requirements to set a backref to any table, it could be
the main table or the table that contains the main table as a foreign key.
2. The backref variable (e.g. backref="nodes") is just a name and it
doesn't refer to a table or model.
3. The backref is the "reference" with which the main table data is
obtained by the model with the foreign key (as per your example given).
That is to say, if the backref of AModel is b, I get AModel by object.b.
4. The naming of the relationship (i.e. nodes = relationship(),
where the nodes is the name), is just that a name and if names are
duplicated in different models, it doesn't matter.
5. A relationship defined by a backref has to be unique.
*nodes = relationship("NodesModel", backref="nodes)*
in a model, I cannot have the same relationship in another model as the
relationship has to be unique if pointing to the same model, so something
like
*nodes = relationship("EdgesModel", backref="nodes")*
would work.
Desmond
On Wed, 29 May 2019 at 20:10, Simon King wrote:
> 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 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 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
Re: [sqlalchemy] Child table with 2 relationships to parent
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 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 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 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 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,
Re: [sqlalchemy] Child table with 2 relationships to parent
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 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 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 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),
>
Re: [sqlalchemy] Child table with 2 relationships to parent
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 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 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 =
Re: [sqlalchemy] Child table with 2 relationships to parent
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:
1. The backref allows the linking of the 2 tables bidirectionally. Does
the foreign_keys in the relationships model do the same thing?
2. 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 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
Re: [sqlalchemy] Child table with 2 relationships to parent
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 wrote:
> On Wed, May 29, 2019 at 10:08 AM Desmond Lim 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,
Re: [sqlalchemy] Child table with 2 relationships to parent
On Wed, May 29, 2019 at 10:08 AM Desmond Lim 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.
