Navigation

    MemFireDB论坛

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups

    小技巧,使用JavaScript进行多表关联查询

    MemFireDB新手区
    2
    2
    23
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • M
      MemFireDB Robot last edited by MemFireDB Robot

      创建subject表记录课程信息

      CREATE TABLE "public"."subject" ( 
        "id" BIGINT NOT NULL,
        "teacherName" TEXT NOT NULL,
        "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
        "adress" TEXT NOT NULL,
        "subjectName" TEXT NOT NULL,
        CONSTRAINT "subject_pkey" PRIMARY KEY ("id")
      );
      INSERT INTO "public"."subject" ("id", "teacherName", "updated_at", "adress", "subjectName") VALUES ('1', '张珊', '2022-08-12 18:28:30.725+08', '计科楼2-1003', '数据库原理');
      INSERT INTO "public"."subject" ("id", "teacherName", "updated_at", "adress", "subjectName") VALUES ('2', '李四', '2022-08-17 16:13:54.527+08', '重楼2-3112', '计算机基础');
      INSERT INTO "public"."subject" ("id", "teacherName", "updated_at", "adress", "subjectName") VALUES ('3', '王五', '2022-08-17 17:28:49.603+08', '4教-101', '分布式数据库');
      INSERT INTO "public"."subject" ("id", "teacherName", "updated_at", "adress", "subjectName") VALUES ('4', '王博', '2022-08-18 17:28:59.265+08', '综合楼4-401', '高等数学');
      

      创建school表记录学校信息

      CREATE TABLE "public"."school" ( 
        "id" BIGINT NOT NULL,
        "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
        "schoolName" TEXT NOT NULL,
        CONSTRAINT "school_pkey" PRIMARY KEY ("id"),
        CONSTRAINT "school_schoolName_key" UNIQUE ("schoolName")
      );
      INSERT INTO "public"."school" ("id", "updated_at", "schoolName") VALUES ('1', '2022-08-12 18:43:53.166+08', '武汉大学');
      INSERT INTO "public"."school" ("id", "updated_at", "schoolName") VALUES ('2', '2022-08-22 11:01:19.088+08', '武汉科技大学');
      INSERT INTO "public"."school" ("id", "updated_at", "schoolName") VALUES ('3', '2022-08-22 11:01:36.909+08', '华中师范大学');
      INSERT INTO "public"."school" ("id", "updated_at", "schoolName") VALUES ('4', '2022-08-22 11:01:56.022+08', '武汉理工大学');
      

      创建subject_student记录学生的课表和学校信息

      CREATE TABLE "public"."subject_student" ( 
        "id" BIGINT NOT NULL,
        "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
        "studentId" BIGINT NOT NULL,
        "schoolId" BIGINT NULL,
        "subjectId" BIGINT NOT NULL,
        CONSTRAINT "subject_student_pkey" PRIMARY KEY ("id")
      );
      --设置subjectId与课程表的id关联和schoolId与学校表的id关联
      ALTER TABLE "public"."subject_student" ADD CONSTRAINT "subject_student_subjectId_fkey" FOREIGN KEY ("subjectId") REFERENCES "public"."subject" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
      ALTER TABLE "public"."subject_student" ADD CONSTRAINT "subject_student_schoolId_fkey" FOREIGN KEY ("schoolId") REFERENCES "public"."school" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
      -- 创建联合唯一索引
      CREATE UNIQUE INDEX subject_student_schoolId_ukey ON "public"."subject_student"("studentId","schoolId","subjectId");
      
      INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('1', '2022-08-17 09:51:14.733+08', '20222201', '1','1');
      INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('2', '2022-08-17 16:14:51.236+08', '20222202', '2','2');
      INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('3', '2022-08-17 17:29:33.938+08', '20222201', '3','3');
      INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('4', '2022-08-18 17:30:09.934+08', '20222202', '4','4');
      

      查询操作

      查询学生的studentId为20222201的课表信息和学校信息。
      当您想要自定义查询主表与关联表的一些字段时,您可以在select里面用逗号把主表的字段隔开,后面加上想要查询的关联表,关联表紧挨着用括号包裹它的字段,也需要用逗号隔开。

      const { data, error } = await supabaseJs
          .from('subject_student')
          .select(`
          id,
          studentId,
          subject (id,subjectName,teacherName,adress),
          school (id,schoolName)
        `).eq("studentId","20222201")
      

      结果:

      [
        {
          id: 1,
          studentId: 20222201,
          subject: {
            id: 1,
            subjectName: '数据库原理',
            teacherName: '张珊',
            adress: '计科楼2-1003'
          },
          school: { id: 1, schoolName: '武汉大学' }
        },
        {
          id: 3,
          studentId: 20222201,
          subject: {
            id: 3,
            subjectName: '分布式数据库',
            teacherName: '王五',
            adress: '4教-101'
          },
          school: { id: 3, schoolName: '华中师范大学' }
        }
      ]
      

      更多用法可以参考: https://document.memfiredb.com/docs/guides/database/associated-query

      S 1 Reply Last reply Reply Quote 0
      • S
        starry @MemFireDB Robot last edited by

        详细教程可以参考https://document.memfiredb.com/docs/guides/database/associated-query:

        1 Reply Last reply Reply Quote 2
        • First post
          Last post