9

I have a table of 'Clients' where a client can be a child of another client.

Here's the table definition.

[ClientID] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[VPFSID] [varchar](50) NOT NULL,
[Type] [varchar](25) NULL,
[ROHostID] [varchar](60) NOT NULL,
[RWHostID] [varchar](60) NOT NULL,
[ParentClientID] [int] NULL

In SQLAlchemy, how do I create the relationship between the ParentClientID and ClientID. I put together this class using declarative but I'm not sure if it's valid or not. A Client can have many children, but can only have a single parent, so it's a Many-to-1 relationship

class Client(Base):
    """ Client Filesystems """
    __tablename__ = 'Client'

    client_id = Column('ClientID', int, primary_key=True, nullable=Flase)
    name = Column('name', String(50), nullable=False)
    vpfs_id = Column('VPFSID', String(50), nullable=False)
    type = Column('Type',String(25))
    ro_host_id = Column('ROHostID', String(60), ForeignKey('DataMover.HostID'), nullable=False)
    rw_host_id = Column('RWHostID', String(60), ForeignKey('DataMover.HostID'), nullable=False)
    rw_host = relation('Datamover',backref="rw_clients")
    ro_host = relation('Datamover',backref="ro_clients")
    parent_client_id = Column('ParentClientID',int,ForeignKey('Client.ClientID'))
    parent = relation('Client',ForeignKey('Client.ClientID'))

Any suggestions on accomplishing this?

Kurt Telep
  • 721
  • 1
  • 5
  • 9
  • 5
    possible duplicate of [sqlalchemy - Mapping self-referential relationship as one to many (declarative form)](http://stackoverflow.com/questions/2638217/sqlalchemy-mapping-self-referential-relationship-as-one-to-many-declarative-fo) – Denis Otkidach Apr 13 '11 at 14:02

2 Answers2

19
class Client(Base):
    ...<snip>...
    parent = relation('Client', remote_side=[client_id])

Docs here: orm/self_referential.html

TobiMarg
  • 3,667
  • 1
  • 20
  • 25
Gary van der Merwe
  • 9,134
  • 3
  • 49
  • 80
0

My Flask recipe

class Comment(db.Model):
    __tablename__ = "comments"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    time = db.Column(db.DateTime, default=datetime.now)
    data = db.Column(db.JSON)

    """ relationships within the same table """
    reply_to_comment_id = db.Column(db.Integer, db.ForeignKey('comments.id'))
    reply_to_comment = db.relationship('Comment', remote_side=[id])

    def __repr__(self):
        return f'<Comment id:{self.id}>'

I have been looking for a long time how to do this in a flask. The example from the comment above helped a lot, but I forgot to set ForeignKey and suffered for a long time with errors.

НеОлег
  • 36
  • 2