On 05/25/2017 12:28 PM, 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:
|
classDrawing(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")
classDrawingNode(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")
classNode(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 *
fromdrawing_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;
that SQL isn't valid as you don't name what dn_2 is but if it's
DrawingNode, this is a string of joins like this:
n1 = aliased(Node)
n2 = aliased(Node2)
d2 = aliased(DrawingNode)
session.query(DrawingNode).join(n1, DrawingNode.node).join(n2,
n1.parent).join(d2, n2.node).filter(DrawingNode.drawing_id == 7)
i.e. it's spelled out just like the SQL.
|
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.
you have an association object pattern so you might be just looking for
the association proxy:
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html?highlight=associationproxy#module-sqlalchemy.ext.associationproxy
association proxy is more about instances in memory, it's less flexible
on the query side where you might have better results just sticking with
join().
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
