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

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

starry

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