Question 218079 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how to create a sorted database OneToMany/ManyToOne relationships

I’m working on a courses Application where the user can sort his content however he like
for example say there is

  • Courses Table – contains course data(title, thumnail…) sub-courses and videos
  • Videos Table – contains video data(video url, title…)

so what I did was creating 2 OneToMany relationships in the Courses Table

  • one that references sub courses inside Courses Table(like parent/children)
  • the other one references videos in the Videos Table

however I’m trying to make the user able to sort them however he like not like a creation date sort but each child has an index and content get’s sorted based on that index in the UI

I’m using a PostgreSQL database

I’m new to sql and databases in general and I don’t know if my design is right if you have an suggestions please tell me

also if you know any good articles or tutorials in this topics please link them

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a solution my self

    I created a separate table called CourseChildren

    • the table has and sort_index column

    • it has 2 nullable relations one with the courses table and one for the videos table

    • I added a check constraint which will ensure that one of course or video relations is not null (not both not neither)

    • I added a composite unique key that will make sure that parentId and sort_index isn't dublicated

    this is the implementation in typeorm

    @Entity({ name: "course_children" })
@Index(["index", "parent"], { unique: true })
@Check(
    `("courseId" IS NOT NULL AND "videoId" IS NULL) 
        OR 
        ("courseId" IS NULL AND "videoId" IS NOT NULL)`,
)
export class CourseChildren extends BaseEntity {
    @PrimaryGeneratedColumn("uuid")
    id!: string;

    @Column()
    sort_index!: number;

    @ManyToOne(() => Course, (course) => course.children)
    parent!: Relation<Course>;

    @Column({ nullable: true })
    courseId!: string;

    @Column({ nullable: true })
    videoId!: string;

    @OneToOne(() => Course, { nullable: true })
    @JoinColumn({ name: "courseId" })
    course!: Relation<Course>;

    @OneToOne(() => Video, { nullable: true })
    @JoinColumn({ name: "videoId" })
    video!: Relation<Video>;
}

@Entity({ name: "courses" })
class Course extends BaseEntity {
    @PrimaryGeneratedColumn("uuid")
    id!: string;

    @Column()
    title!: string;

    ...
    @OneToMany(() => CourseChildren, (children) => children.course)
    children!: Relation<CourseChildren>[];
}

  2. 0

    You can add a new column in both tables which stores the sorting order.

    Lets call this column "sort_index". Whenever a user changes the sort order from frontend, you just update this index value for courses and videos.

    Something like this (applicable to both courses & videos):

    course_id title sort_index
    1 course-1 2
    2 course-2 1
    3 course-3 3

    Next, you just need to display it on UI as per the index.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search