1

I have an Node object that should relate to other Node objects - described by the parents and children relationships. I cannot use an association table because I want to store some additional data together with the relation (type of relation, etc).

That way, two nodes can be related to each other with different types of relations. What I want to achieve is to get only unique objects related to each other - meaning that I only care if the nodes are related by at least one relation.

For now, here's my simplified code for the node object. Using association proxy, I can create association objects with additional data using creator method.

class Node(Base):
    __tablename__ = "node"

    id = Column(Integer, primary_key=True)

    ...

    children = association_proxy(
        "related_children",
        "child",
        creator=lambda input: Relation(
            parent=input.parent,
            child=input.child,
            type=input.type,
        ),
    )

    parents = association_proxy(
        "related_parents",
        "parent",
        creator=lambda input: Relation(
            parent=input.parent,
            child=input.child,
            type=input.type,

        ),
    )

In the Relation, I have two relationships for both parent and child of the relation.

class Relation(Base):
    __tablename__ = "relation"

    id = sql.Column(sql.Integer, primary_key=True)
    parent_id = sql.Column(sql.Integer, sql.ForeignKey("node.id"))
    child_id = sql.Column(sql.Integer, sql.ForeignKey("node.id"))
    type = sql.Column(sql.String)
    ...

    parent = relationship(
        "Node", foreign_keys=[parent_id], backref=backref("related_children")
    )
    child = relationship(
        "Node", foreign_keys=[child_id], backref=backref("related_parents")
    )

The problem is, if I have two nodes, parent and a child, connected by two and more relations, after I get a list of children from the Node.children, it will return the same child objects as many times as there are relationships between these two nodes.

I know it's just possible to write a custom query with distinct as a property of the Node, or just convert the return list to set to eliminate the duplicates, but is there a more SQLAlchemy way of doing such case?

W3ndige
  • 11
  • 1
  • Where did you get the ides that an association table could not contain additional data - it can and is not unusual. The additional data is typically termed "intersection date" as it is usually meaningless except as part of the association table (it being the intersection of the tables). I admit that I do not know SQLAlchemy so maybe that was the issue. But a google search quickly turned up an [example](https://www.pythoncentral.io/sqlalchemy-association-tables/) of it. The column is even called extra_data.See "class DepartmentEmployeeLink: – Belayer Feb 20 '21 at 00:54
  • 1
    @Belayer I'm not as familiar with SQLalchemy as I would want (yet!), but the example you provided is using association object as to my understanding. In SQLAlchemy the association table is created using `sql.Table` while the association object is completely new model just as in my case. In particular, I'm refering to [this](https://stackoverflow.com/questions/35795717/flask-sqlalchemy-many-to-many-relationship-with-extra-field) and [documentation](https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object). – W3ndige Feb 20 '21 at 08:03
  • Why do you want to write a custom query? You can also use `session.query(Node.child).distinct().filter(Node.id=1)` – rfkortekaas Feb 20 '21 at 10:23
  • @rfkortekaas Could you expand please? My `Node` model does not have a `child` or a `parent` memebers, instead it has `children` and `parents`. While trying your method, I get an attribute error `AttributeError: type object 'Node' has no attribute 'child'` – W3ndige Feb 20 '21 at 12:28
  • Okay, with some inspiration from your answer I was able to come up with this cursed query `db.query(models.Node).distinct().filter(models.Node.children.any(models.Relation.child_id == 1)).all()`, for the parents of the node. Seems to work at first sight, but I'll report later in the day if it's good enough for the solution. – W3ndige Feb 20 '21 at 12:58
  • @W3ndige, as I said I do not know SQLAlchemy. The point is it does support additional data on intersection tables. Thus making your contention "cannot use an association table because I want to store some additional data" a false statement. – Belayer Feb 20 '21 at 19:56

0 Answers0