I can get a lazy load to do this:
SELECT node.id AS node_id, node.label AS node_label
FROM node, node_to_node
WHERE ? = node_to_node.right_node_id AND node_to_node.left_node_id IN
(SELECT node_to_node.left_node_id
FROM node_to_node
WHERE node_to_node.right_node_id = node.id)
which is equivalent, except that it is using correlation and not a
bound parameter, just setting up correlation:
sibling_nodes = relationship(
"Node",
secondary=node_to_node,
primaryjoin=id == node_to_node.c.right_node_id,
secondaryjoin=node_to_node.c.left_node_id.in_(
select([node_to_node.c.left_node_id]).where(
node_to_node.c.right_node_id == id).
correlate_except(node_to_node)
),
viewonly=True,
)
I didn't go beyond that for the moment but you can try playing with
the example below, thanks for the clear test
from sqlalchemy import (select, create_engine, Integer, ForeignKey, String,
Column, Table)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
import random
Base = declarative_base()
node_to_node = Table("node_to_node", Base.metadata,
Column("left_node_id", Integer,
ForeignKey("node.id"), primary_key=True),
Column("right_node_id", Integer, ForeignKey("node.id"),
primary_key=True)
)
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
label = Column(String)
right_nodes = relationship(
"Node",
secondary=node_to_node,
primaryjoin=id == node_to_node.c.left_node_id,
secondaryjoin=id == node_to_node.c.right_node_id,
backref="left_nodes"
)
# WHERE node_to_node.right_node_id = node.id AND node_to_node.left_node_id IN
# (SELECT node_to_node.left_node_id AS node_to_node_left_node_id
# FROM node_to_node
# WHERE node_to_node.right_node_id = ?)
# This is my attempt to create a sibling relationship based on a query below
sibling_nodes = relationship(
"Node",
secondary=node_to_node,
primaryjoin=id == node_to_node.c.right_node_id,
secondaryjoin=node_to_node.c.left_node_id.in_(
select([node_to_node.c.left_node_id]).where(
node_to_node.c.right_node_id == id).
correlate_except(node_to_node)
),
viewonly=True,
)
def __repr__(self):
return "({0}) {1}".format(self.id, self.label)
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
predecessors = ["one", "two", "three", "four", "five"]
peers = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"]
successors = ["1", "2", "3", "4", "5"]
session.add_all([Node(label=l) for l in predecessors + peers + successors])
for p in peers:
r = session.query(Node).filter(Node.label == p).first()
r.left_nodes = session.query(Node).filter(Node.label.in_(
random.sample(predecessors, 1))).all()
session.commit()
for s in successors:
r = session.query(Node).filter(Node.label == s).first()
r.left_nodes = session.query(Node).filter(Node.label.in_(
random.sample(peers, 1))).all()
session.commit()
# The query reporting to p is the result I want and I have tried unsuccessfully
# to translate it into a relationship see above sibling_nodes in Node class
for l in peers:
r = session.query(Node).filter(Node.label == l).first()
r.sibling_nodes
# r = session.query(Node.id).filter(Node.label == l).scalar()
# p = session.query(Node).filter(
# node_to_node.c.right_node_id == Node.id).filter(
# node_to_node.c.left_node_id.in_(
# session.query(node_to_node.c.left_node_id).filter(
# node_to_node.c.right_node_id == r)
# )
# ).all()
# print(p)
# =============================================================================
# SELECT node.id AS node_id, node.label AS node_label
# FROM node, node_to_node
# WHERE node_to_node.right_node_id = node.id AND node_to_node.left_node_id IN
# (SELECT node_to_node.left_node_id AS node_to_node_left_node_id
# FROM node_to_node
# WHERE node_to_node.right_node_id = ?)
# SELECT node.id AS node_id, node.label AS node_label
# FROM node, node_to_node
# WHERE ? = node_to_node.right_node_id AND node_to_node.left_node_id IN
# (SELECT node_to_node.left_node_id
# FROM node_to_node
# WHERE node_to_node.right_node_id = node.id)
# 2018-12-11 21:17:43,859 INFO sqlalchemy.engine.base.Engine (11,)
# =============================================================================
On Tue, Dec 11, 2018 at 8:57 AM Kurt Forrester
<[email protected]> wrote:
>
> I am trying to create a table attribute via a relationship. It needs to be
> ready-only and return the nodes (peer) where which have at least one common
> left node (predecessor). I am able to construct a query (shown below at the
> bottom) but I am having difficultly working out where I am going wrong when
> translating it into a relationship (if it is at all possible). Below is some
> code to demonstrate what I am trying to achieve. Many thanks.
>
>
> from sqlalchemy import (select, create_engine, Integer, ForeignKey, String,
> Column, Table)
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker
>
> import random
>
> Base = declarative_base()
>
> node_to_node = Table("node_to_node", Base.metadata,
> Column("left_node_id", Integer,
> ForeignKey("node.id"), primary_key=True),
> Column("right_node_id", Integer, ForeignKey("node.id"),
> primary_key=True)
> )
>
>
> class Node(Base):
> __tablename__ = 'node'
> id = Column(Integer, primary_key=True)
> label = Column(String)
> right_nodes = relationship("Node",
> secondary=node_to_node,
> primaryjoin=id==node_to_node.c.left_node_id,
> secondaryjoin=id==node_to_node.c.right_node_id,
> backref= "left_nodes"
> )
>
> # This is my attempt to create a sibling relationship based on a query below
> sibling_nodes = relationship("Node",
> secondary=node_to_node,
> primaryjoin=id==node_to_node.c.right_node_id,
> secondaryjoin=node_to_node.c.left_node_id.in_(
> select([node_to_node.c.left_node_id]).where(
> node_to_node.c.right_node_id == id)),
> viewonly=True,
> )
>
> def __repr__(self):
> return "({0}) {1}".format(self.id, self.label)
>
>
> engine = create_engine('sqlite:///:memory:', echo=False)
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
> session = Session()
>
> predecessors = ["one", "two", "three", "four", "five"]
> peers = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"]
> successors = ["1", "2", "3", "4", "5"]
>
> session.add_all([Node(label=l) for l in predecessors + peers + successors])
>
> for p in peers:
> r = session.query(Node).filter(Node.label == p).first()
> r.left_nodes = session.query(Node).filter(Node.label.in_(
> random.sample(predecessors, 1))).all()
> session.commit()
>
> for s in successors:
> r = session.query(Node).filter(Node.label == s).first()
> r.left_nodes = session.query(Node).filter(Node.label.in_(
> random.sample(peers, 1))).all()
> session.commit()
>
> # The query reporting to p is the result I want and I have tried
> unsuccessfully to translate it into a relationship
> # see above sibling_nodes in Node class
> for l in peers:
> r = session.query(Node.id).filter(Node.label == l).scalar()
> p = session.query(Node).filter(node_to_node.c.right_node_id ==
> Node.id).filter(node_to_node.c.left_node_id.in_(
>
> session.query(node_to_node.c.left_node_id).filter(
> node_to_node.c.right_node_id ==
> r))).all()
>
> print(p)
>
> #
> =============================================================================
> # SELECT node.id AS node_id, node.label AS node_label
> # FROM node, node_to_node
> # WHERE node_to_node.right_node_id = node.id AND node_to_node.left_node_id IN
> # (SELECT node_to_node.left_node_id AS node_to_node_left_node_id
> # FROM node_to_node
> # WHERE node_to_node.right_node_id = ?)
> #
> =============================================================================
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
