How to better understand `remote_side` in sqlalchemy?

[Jinghui Niu]

I've been studying sqlalchemy's self referential table. I've read the documentation many times and still have difficulties understanding the concept of remote_side. Could someone please draw a diagram or use an analogy to help explain this concept? I think visualizing is a better way but anything helps would be appreciated. Thanks.

By the way, in my opinion the word remote is a little vague, as it can be interpreted from different angles. Like the word left and rigt, it really depends on which direction you are facing, my right could be your left. I'm not really confident in this but I would guess chaing the name from remote_side to many_side may help? Correct me if I'm wrong here.

[Jonathan Vanasco]

'many'  is the same as 'remote' in the example that you are thinking of, but it loses applicability if you are doing a one-to-one relationship.

Let me illustrate with the `remote` annotation, which is a corollary form of `remote_side` in the relationships API, and tends to be more clear:

(http://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.remote_side)

Let's say you a self-referential table like the following:

    CREATE TABLE node (
        id SERIAL PRIMARY KEY
        id_parent INT REFERENCES node(id)
    )

if we want to make a sqlalchemy class that describes this with a relationship, we need to use `remote` or `remote_side` to indicate how columns on the object we fetch  match up to the objects it is related to

the sqlalchemy class might look like this:

     class Node(Base):
         id = Column(Integer, primary_key=True)
         parent_id = Column(Integer, nullable=True, ForeignKey('node.id'))

         parent = relationship("Node", primary_join="Node.parent_id==remote(Node.id)", uselist=False)
         child = relationship("Node", primary_join="Node.id==remote(Node.parent_id)", uselist=False)

this is basically the same example as in the self_referential docs (http://docs.sqlalchemy.org/en/latest/orm/self_referential.html), just using the alternate syntax and specifying  a one-to-one.  

[Mike Bayer]

On Thu, Jul 20, 2017 at 1:34 PM, Jinghui Niu <niuji...@gmail.com> wrote:
> I've been studying sqlalchemy's self referential table. I've read the
> documentation many times and still have difficulties understanding the
> concept of remote_side. Could someone please draw a diagram or use an
> analogy to help explain this concept? I think visualizing is a better way
> but anything helps would be appreciated. Thanks.
>
>
> By the way, in my opinion the word remote is a little vague, as it can be
> interpreted from different angles. Like the word left and rigt, it really
> depends on which direction you are facing, my right could be your left.

it's more specific than "left" and "right", because if you have:

Node.relationship(remote_side=something) -> Node

the "remote_side" refers specifically to the columns that are not
local to the Node that has the .relationship.

That is:

Node.col1
Node.col2
Node.relationship:
name='foo'
local_side=Node.col1
remote_side=Node.col2


You then know that:

query(Node).join(Node.foo, aliased=True)

will emit the equivalent of:

SELECT * FROM node JOIN node AS node_remote ON node.col1 = node_remote.col2

I'm
> not really confident in this but I would guess chaing the name from
> remote_side to many_side may help? Correct me if I'm wrong here.
>

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jinghui Niu]

I see. But still I'm struggling to see the real difference between:

Node.parent_id = Node.id

vs.

Node.id = Node.parent_id

Aren't we just switching around sides here?

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Mike Bayer]

On Thu, Jul 20, 2017 at 8:10 PM, Jinghui Niu <niuji...@gmail.com> wrote:
> I see. But still I'm struggling to see the real difference between:
>
> Node.parent_id = Node.id
> vs.
> Node.id = Node.parent_id
>
> Aren't we just switching around sides here?

those two conditions are equivalent. but that's not how you would
join a table to itself self-referentially. you always need at least
one of the tables to be *aliased*, so that it represents a distinct
set of rows:

SELECT * FROM node AS n_left JOIN node AS n_right ON
n_left.parent_id = n_right.id

can you see the difference?

>> > email to sqlalchemy+...@googlegroups.com.

>> > To post to this group, send email to sqlal...@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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to

>> sqlalchemy+...@googlegroups.com.

>> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Jinghui Niu]

Now I see, thanks Mike!

>> > email to sqlalchemy+unsubscribe@googlegroups.com.

>> > To post to this group, send email to sqlal...@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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to

>> sqlalchemy+unsubscribe@googlegroups.com.

>> To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/e68eFOoG3LQ/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.