小技巧,使用JavaScript进行多表关联查询
-
创建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
-
详细教程可以参考https://document.memfiredb.com/docs/guides/database/associated-query: