How to better understand `remote_side` in sqlalchemy?

Question

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.

Edit: 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.

Answer 1

Since no one seems to be interested in such a beginner's concept, I will try my best to attack this.

First visualize two identical tables, one on the left and the other on the right. Now take a deep breath.

By specifying remote_side to the attribute that the foreign key refering to, you actually establishing a relationship that from the local to the relmote.

Now the question is, can I specify the remote_sed to the foreign key attribute itself? Let us see the difference by doing an experiment:

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer(), primary_key=True)
    manager_id = Column(Integer(), ForeignKey('employees.id'))
    name = Column(String(255), nullable=False)
    Manager = relationship("Employee", backref=backref('reports'), remote_side=[manager_id])

Running this, we get this error:

sqlalchemy.exc.ArgumentError: Employee.Manager and back-reference Employee.reports are both of the same direction symbol('ONETOMANY').  Did you mean to set remote_side on the many-to-one side ?

So apparently, this doesn't work. We are locked in a one-way tyranny.

Answer 2

When specifying a relationship between two tables, one of the tables has a foreign key. This foreign key "marks" one or more columns as being "connected" to columns in another table.

When specifying relationships in SQLAlchemy, we're using the term "remote" when talking about the columns "on the other side" of the foreign-key columns. Or, whichever columns the "foreign" columns are "linked to".

For example, consider a "Customer" table with a "Order" table:

+---------------+         +------------------+
|  Customer     |         | Order            |
+---------------+         +------------------+
|  customer-id  | ------- | customer_id [FK] |
|  name         |         | order_id         |
+---------------+         +------------------+

Assume the order table has a relationship with the customer table via the customer_id column.

In that case, the foreign key will be located on the Order table, and it will "point to" the Customer.customer_id column.

As we established earlier, the "remote side" in SQLAlchemy is the "other side" of the foreign key.

So in this particular example, the "remote side" is the Customer.customer_id column.

When working with self-referential tables the concept is the same. You have some columns defined as foreign keys "pointing to" other columns (but in the same table this time). But the concept of "foreign" and "remote" remains the same for SA. You just have to adapt it as necessary for the self-referential relationship.