Dear Mike,
Thank you for the prompt response and concise changes. It now works
perfectly for me. Hopefully somebody else will also find this useful.
Kind regards,
Kurt
On Wednesday, December 12, 2018 at 2:21:21 AM UTC, Mike Bayer wrote:
>
> 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] <javascript:>> 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] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > 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.
