CODEWITHSUNDEEP
python-3.xsqlalchemy
Chris
Chris

Reputation: 397

Many to one relationships within table in SqlAlchemy

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

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

Upvotes: 0

Views: 298

Answers (1)

Chris
Chris

Reputation: 397

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.

Upvotes: 1

Related Questions