13

The questions below are related to database table relationships and the abstractions which SQLAlchemy provides for it.

  1. What is the difference between remote and local side?
  2. If there is remote_side then why not a local_side?
  3. In the example given here how is parent_id "local" side?
  4. remote_side takes in a list so what are the elements of that list supposed to be? And if their are more then one elements then what exactly does that signify?

I have read the docs several time but fail to understand the basic concept behind it and how to use it appropriately. (Almost) All I know is that it is supposed to convert a one-to-many relationship into many-to-one. And usually when I try to use it, where I feel its relevant, I end introducing ambiguities which SQLAlchemy complains about, which in majority of the cases is fixed by removing the remote_side argument all together.

Bleeding Fingers
  • 6,993
  • 7
  • 46
  • 74

1 Answers1

15

What is the difference between remote and local side?

given a model like:

class Parent(Base):
    # ...
    id = Column(Integer, primary_key=True)
    children = relationship("Child")

class Child(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))

Regarding the relationship Parent.children, columns that are present on Parent are the local side, columns that are present on Child are the remote side.

This seems a bit trivial, and only becomes something interesting when you have a so-called "self-referential" relationship, where both sides refer to the same table:

class Parent(Base):
    # ...
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    children = relationship("Parent")

Where above, Parent.id is the local side of Parent.children and Parent.parent_id is the remote side, based on Parent -> .children -> Parent considering the left side to be "local" and the right side to be "remote".

If there is remote_side then why not a local_side?

There is a local side, if you were to say Parent.children.property.local_side you'd see it. remote_side and local_side are only things that the relationship needs to worry about, and remote_side is public as something you can set only for the purposes of giving relationship a hint with a self referential relationship; nothing else.

In the example given here how is parent_id "local" side?

If you have Node.parent, that looks like Node --> .parent --> Node. "local" means the left side and "remote" is the right. The way a many-to-one self referential joins is like Node.parent_id = Node.id, so parent_id is local.

remote_side takes in a list so what are the elements of that list supposed to be? And if their are more then one elements then what exactly does that signify?

It's a list because in SQLAlchemy all primary and foreign keys can potentially be composite, meaning, consists of more than one column. In the typical case of a surrogate key, it's a list of one element.

Overall, you should never need to use remote_side except in the very specific case of a self-referential relationship that is many-to-one. Otherwise it should never be needed.

Bleeding Fingers
  • 6,993
  • 7
  • 46
  • 74
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • wouldn't `Parent` complain while creating the `children` property, since it doesn't FK it? – Bleeding Fingers May 24 '13 at 19:16
  • 1
    the FKs are different from the "remote". FKs refer to which database table refers to the other in ownership sense. "local" and "remote" refer to which database table's perspective you're looking from (in a self referential relationship, its tricker, but perhaps pretend there's really two tables - since we are really talking about two different rows in any case). – zzzeek May 24 '13 at 20:30
  • @zzzeek Has the API changed? I can't see, to find the `property` attribute on children. – Games Brainiac Oct 31 '14 at 05:08
  • @zzzeek, excellent explanation. Thanks Mike! I have the same question about `property.remote_side`, can't find it. – Jinghui Niu Jul 20 '17 at 22:57