On 02/22/2017 11:28 AM, Matthew Brookes wrote:
Hi Mike,
Thanks for the pointers. I take your point about directionality, but it
feels like this is a special case that intuitively should work the same
way for a single model that it does for two. However for now, it does
what it does.
SQLAlchemy is thinking relationally here. The load of A->B implies a
very simple kind of SELECT and in the case of two separate foreign keys
here it does not, it implies something much more involved e.g. a UNION
of two selects.
I took at look at using a union @property, and while it does work to
return both sides of the relationship, it does not, as you say support
querying. I took a look at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
<http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper>,
and I"ll be honest, I didn't understand most of it. Also
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#building-query-enabled-properties
seems to suggest that only predefined queries would be possible?
I'm wondering if instead I should use one-to-many relationships, and use
an event to create the opposing relationship when a relationship is
added? Would appreciate your thoughts.
A lot of times people just store two association rows in either
direction. This is the case that I've used when I've built social
networks that are storing "friends" - it's usually ends up being a good
idea that you store "A is a friend of B" separately from "B is a friend
of A", since later on you can add modifiers like "A says they're a
friend from high school" and "B says they're a friend from college",
sort of thing.
Thanks!
Matt.
On Tuesday, 21 February 2017 23:25:27 UTC, Mike Bayer wrote:
you want "Node.connected" to be the set of all nodes connected in
either
direction. The problem is that relationally, all graphs are
"directed", so we have the "left", "right" aspect of things.
The easiest way to get ".connected" as the union of both sets
(hint!) is
to just union them in Python. Give Node a "left_nodes" and
"right_nodes" relationship (example: see
http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html
<http://docs.sqlalchemy.org/en/latest/_modules/examples/graphs/directed_graph.html>)
then make a regular Python @property that returns
"self.left_nodes.union(self.right_nodes)", easy enough.
If you want to get into querying this relationally, then you need to do
the UNION on the SQL side and you'd need to get into creating a mapping
against a UNION and then building a relationship to it. This is
significantly more involved and would be using some of the
techniques at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
<http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper>.
But, a @property that does a Python union of two relationships, quick
and easy.
On 02/21/2017 04:43 PM, Matthew Brookes wrote:
>
> I apologize if this is well trodden ground, but having googled, and
> stack-overflowed, read the docs, and searched this list, where
lots of
> people have asked the same (or similar) questions, I couldn't find a
> concrete answer...
>
> I'm trying to set up a Model such that an entry can be connected
to one
> or more other entries, and that the reverse relationship can be found
> from the same attribute.
>
> Effectively I'm trying to do this:
>
> ```
>
> from sqlalchemy import Integer, ForeignKey, String, Column, Table
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
>
> Base = declarative_base()
>
> node_to_node = Table("node_to_node", Base.metadata,
> Column("left_node_id", Integer, ForeignKey("node.id
<http://node.id>"), primary_key=True),
> Column("right_node_id", Integer, ForeignKey("node.id
<http://node.id>"), primary_key=True)
> )
>
> class Node(Base):
> __tablename__ = 'node'
> id = Column(Integer, primary_key=True)
> label = Column(String)
> connected = 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="connected"
> )
>
> ```
>
> However, that naturally fails (`Error creating backref
'translations' on
> relationship 'Sentence.translations': property of that name
exists`) as
> there's no magic to figure out that `translations` should be
> bi-directional.
>
> Is there another way to achieve this?
>
> Thanks!
>
> Matt.
>
> --
> 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
<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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<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.
