0

I am trying to create a model class Document which optionally contains 2 pointers to 2 other elements of the same class (table).

  • original_id which should hold the index of another member of the table which is "the" original version of the document (can be null).
  • first_id which should hold the index of another member of the table which is the "first" document that was stored (can be null).

I defined my class as

class Document(Base):
    __tablename__ = 'documents'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    original_id = Column(Integer, ForeignKey('documents.id'))
    original = relationship('Document', foreign_keys= original_id)
    
    first_id = Column(Integer, ForeignKey('documents.id'))
    first = relationship('Document', foreign_keys= first_id)

    def __repr__(self):
        return f'{self.name} ({self.id})'

But when I test it as follows:

d1 = Document(name='Dee one')
d2 = Document(name='Dee two')
d1.original.append(d2) # d1 has for original document d2

I am surprised by the implementation in terms of ids stored in what row (after committing)

>>> d1.original_id
None
>>> d2.original_id
1
>>> d1.original
[Dee two (2)]
>>> d2.original
[]

I wanted d1.original_id to hold 2 (the index for Dee two).

Obviously I am doing something wrong, but I am struggling to understand what. It seems I am ending up with a many to one relation but in the wrong direction.

I am using SQLAlchemy 1.3.5

Chris
  • 387
  • 1
  • 8
  • In a one-to-many relationship the foreign key value for the parent object is stored in the child object. Notice that the original_id is stored in d2 , but d1.original (correctly) returns the d2 object. – Gord Thompson Jul 20 '20 at 11:43
  • Yes I but want a many to one relationship, and cannot figure out how to do this in this case. – Chris Jul 20 '20 at 17:52
  • One-to many and many-to-one are the same thing. They both involve one parent object with zero or more child objects. Parent has a one-to-many relationship with Child, and Child has many-to-one with Parent. In your case d1 is the child, and d2 is the parent. – Gord Thompson Jul 20 '20 at 17:54
  • I had found that solution https://stackoverflow.com/questions/5649385/many-to-one-relationship-with-sqlalchemy-in-the-same-table but it only works for one link/pointer/relation. When there are multiple ones, SQLAlchemy complains. Hence my example with 2 fields original and field. But I have finally found a solution. I'll write it down. Many thanks for your remarks. – Chris Jul 20 '20 at 18:09

1 Answers1

1

Originally, I wanted to have a class that contains 2 nullable pointers to other Nodes. To make the relationship clearer, let's call the class Person, and the links Father and Mother. Every person has at most a known Father and a known Mother.

So following the docs, I created the class as

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    father_id = Column(Integer, ForeignKey('persons.id'))
    father = relationship('Person')

When doing so, the father relationship is 1-to-many, i.e. a Person can have many fathers, and a father can be only the father of 1. Which is not what is expected.

Following Many to One relationship with SQLAlchemy in the same table I found a way to make that a 1 to many relationship. Enter the remote_side parameter:

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    father_id = Column(Integer, ForeignKey('persons.id'))
    father = relationship('Person', remote_side=id)
                                    ^^^^^^^^^^^^^^

Now the relationship is as expected. However with multiple links (father and mother), this fails again. I.e. if I refine the class as:

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    father_id = Column(Integer, ForeignKey('persons.id'))
    father = relationship('Person', remote_side= id)
    
    mother_id = Column(Integer, ForeignKey('persons.id'))
    mother = relationship('Person', remote_side= id)

SQLAlchemy fails with an "AmbiguousForeignKeysError". The solution is then to specify what field is used to link to father and mother, as follows:

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    father_id = Column(Integer, ForeignKey('persons.id'))
    father = relationship('Person', remote_side= id, foreign_keys=father_id)
                                                     ^^^^^^^^^^^^^^^^^^^^^^   
    mother_id = Column(Integer, ForeignKey('persons.id'))
    mother = relationship('Person', remote_side= id, foreign_keys=mother_id)
                                                     ^^^^^^^^^^^^^^^^^^^^^^

Now that works as expected.

So in some way, this is an extension of the solution provided in Many to One relationship with SQLAlchemy in the same table

Many thanks Gord Thomson for your remarks.

Chris
  • 387
  • 1
  • 8