Child table with 2 relationships to parent

[Desmond Lim]

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?

Thanks for the help.

Desmond

[Simon King]

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

[Desmond Lim]

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

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@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.

[Desmond Lim]

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

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzagzkvG2fwY3VtZNQR_6gBQsUFvmxkr7pBDJAZmgpY1Feg%40mail.gmail.com.

[Desmond Lim]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfuvzX3hXAu6dbKCRM%2BhQdKMt2d9c7hLecy2UOu1uUEgg%40mail.gmail.com.

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzaj9h%3DZCoeVL9aL%2BPP66xKM8GjWBE%2BUVRW2OcrEoCPyKKg%40mail.gmail.com.

[Desmond Lim]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexf2qL4ih3n0SA4-p%3DcWd4tU-HoRDG5BDyTUEi3V8XPP0g%40mail.gmail.com.

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzagn8UBRe9sSP8_uvfo%3DMisaWM6QMAQSoVpeKfMjA1DH7g%40mail.gmail.com.