CODEWITHSUNDEEP
sqlalchemyfastapipydantic
Muhammad Yousuf
Muhammad Yousuf

Reputation: 178

FastAPI many to many Response Schema and Relationship

I am new to FastAPI and SQL-Alchemy, I am having trouble creating a schema and relationship of the many-to-many table (association table).

Here is an example, a student can enroll in multiple courses and a single course can have multiple students thus making many-to-many relationship.

Database Models

class Student(Base):
    __tablename__ = "student"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)


class Course(Base):
    __tablename__ = "course"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)


class StudentCourse(Base):
    __tablename__ = "student_course"

    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(Integer, ForeignKey("student.id"), nullable=False)
    course_id = Column(Integer, ForeignKey("course.id"), nullable=False)

What relation do I need to define and how to design a schema from which I can get the following responses:

Response 1

[
  {
    "id": 1,
    "name": "Student A",
    "courses": [
      {
        "id": 1,
        "name": "Course A"
      },
      {
        "id": 2,
        "name": "Course B"
      }
    ]
  },
  {
    "id": 2,
    "name": "Student B",
    "courses": [
      {
        "id": 1,
        "name": "Course A"
      },
      {
        "id": 3,
        "name": "Course C"
      }
    ]
  },
  {...}
]

Response 2

[
  {
    "id": 1,
    "name": "Course A",
    "students": [
      {
        "id": 1,
        "name": "Student A"
      },
      {
        "id": 2,
        "name": "Student B"
      }
    ]
  },
  {
    "id": 2,
    "name": "Course B",
    "students": [
      {
        "id": 1,
        "name": "Student A"
      },
    ]
  },
  {
    "id": 3,
    "name": "Course C",
    "students": [
      {
        "id": 2,
        "name": "Student B"
      },
    ]
  },
  {...}
]

Upvotes: 2

Views: 8224

Answers (1)

kalogik
kalogik

Reputation: 31

In my opinion, it is simpler and preferable to use an association table instead of an association object for this many-to-many relationship, since you don't need extra fields. So, according to the documentation, your database models should be:

student_course = Table("student_course", Base.metadata,
                       Column("student_id", ForeignKey("student.id"), primary_key=True),
                       Column("course_id", ForeignKey("course.id"), primary_key=True))

class Student(Base):
    __tablename__ = "student"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    courses = relationship("Course",
                           secondary=student_course,
                           back_populates="students")


class Course(Base):
    __tablename__ = "course"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    students = relationship("Student",
                            secondary=student_course,
                            back_populates="courses")

and your pydantic models (schemas):

class Student(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True


class Course(BaseModel):
    id: int
    name: str

    class Config:
        orm_mode = True


class StudentOut(Student):
    courses: List[Course]


class CourseOut(Course):
    students: List[Student]

where StudentOut and CourseOut are your response models

Upvotes: 3

Related Questions