In my last code block I have a hard-coded drawing_id of 7 from a bad copy/paste, but this would just be the DrawingNode's drawing_id.
On Thursday, May 25, 2017 at 11:28:48 AM UTC-5, SB wrote: > > 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.