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.
