FastAPI / SQLAlchemy - link users together

user_19215883 0
Viewed 100

I have a User table and I want to link users together to create a relationship. A user can be link to many other users (I need to show current and old relationships).

So my first try is to create a table called 'relationship' with 2 columns (user1 and user2) and a column that tell us if the relationship is still active. This is the SQL schemas

USER

id firstname gender
1 John M
2 Marie F
3 Jane F

Relationship

user1 user2 active
1 2 0
1 3 1

Is it the best solution ? Can you explain me how to set it up with SQLAlchemy, and how to set up models and shema in FastAPI ? Especially the relations that allow me to get all relationships at once for a user for example

This is my first try

Models

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database import Base

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, index=True)
    firstname = Column(String)
    gender = Column(String, nullable=True)

    relationships = relationship('Relationship') #What Here ??

class Relationship(Base):
    __tablename__ = "relationship"

    user1 = relationship("User") #back_populates ?
    user2 = relationship("User") #back_populates ?
    active = Column(Boolean, default="true")

Schemas

from pydantic import BaseModel

class Relationship(BaseModel):
    user1: int
    user2: int
    active: bool

    class Config:
        orm_mode = True


class User(BaseModel):
    id: int
    firstname: str
    gender: str
    relationships: List[Relationship] = [] #Is it good ?

    class Config:
        orm_mode = True
fastapi sqlalchemy python
1 Answers

you doesn't need add multiple user into your relationship model

just make a relation to user model

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, index=True)
    firstname = Column(String)
    gender = Column(String, nullable=True)

    relationship = relationship('Relationship', back_populates="user")

class Relationship(Base):
    __tablename__ = "relationship"

    user_id = Column(Integer, ForeignKey("user.id"))
    user = relationship("User", back_populates='relationship')
    active = Column(Boolean, default="true")

from pydantic import BaseModel

class Relationship(BaseModel):
    user_id: int
    active: bool

    class Config:
        orm_mode = True


class User(BaseModel):
    id: int
    firstname: str
    gender: str
    relationships: List[Relationship] = [] #Is it good ?

    class Config:
        orm_mode = True

now in your orm just filter which users are active as True

user_132130710
Related