I'm using SQLAlchemy in Flask and I'm trying to map a relationship between
one point and another, but can't figure it out myself and am looking for
help.
I have Nodes that are physical things that only need to be defined once and
then many instances of those Nodes can be put onto drawings. I have a set
of tables like this:
class Drawing(db.Model):
__tablename__ = "drawing"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(63), unique=True, nullable=False)
drawing_nodes = db.relationship('DrawingNode', back_populates="drawing",
cascade="all, delete-orphan")
class DrawingNode(db.Model):
__tablename__ = "drawing_node"
id = db.Column(db.Integer, primary_key=True)
drawing_id = db.Column(db.Integer, db.ForeignKey('drawing.id'), nullable
=False)
node_id = db.Column(db.Integer, db.ForeignKey('node.id'), nullable=False
)
x = db.Column(db.Integer, nullable=False)
y = db.Column(db.Integer, nullable=False)
__table_args__ = (db.UniqueConstraint('drawing_id', 'node_id', name=
'_drawing_node_uc'),)
node = db.relationship("Node", back_populates="drawing_node", lazy=
"joined")
drawing = db.relationship("Drawing", back_populates="drawing_nodes")
class Node(db.Model):
__tablename__ = 'node'
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('node.id'), default=None
)
type = db.Column('type', db.String(50), nullable=False)
__mapper_args__ = {'polymorphic_on': type, 'with_polymorphic': '*'}
drawing_node = db.relationship('DrawingNode', back_populates="node",
cascade="all, delete-orphan", lazy="dynamic")
parent = db.relation('Node', remote_side=[id], backref=backref(
"children", cascade="all,delete"))
I can currently go from a Drawing to its DrawingNodes without issue and can
go from DrawingNodes to their Nodes and back. I would like to do two things:
1. In DrawingNode, add "children" such that it will find the DrawingNode's
Node, find that Node's children (through Node.parent_id), and then find
those Nodes' DrawingNodes. I can accomplish this with the following
postgres query, but can't figure out how to turn it into a SQLAlchemy
statement:
SELECT *
from drawing_node AS dn_1
INNER JOIN node AS node_1 ON node_id=node_1.id
INNER JOIN node AS node_2 ON node_1.parent_id=node_2.id
INNER JOIN dn_1 AS dn_2 ON dn_2.node_id=node_2.id AND dn_2.drawing_id=dn_1.
drawing_id
WHERE dn_1.drawing_id=7;
2. In Drawing, modify "drawing_nodes" such that it also joins the
information from that DrawingNode's Node in the relationship. I'm less
concerned about this one because I think I'm missing something obvious and
have a workaround, but I figured I'd ask while I'm here.
Any help with this would be great.
Thanks,
SB
--
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.
For more options, visit https://groups.google.com/d/optout.
