关联查询

多表关联是指在关系型数据库中，通过多个数据表之间的字段关联，实现数据的联合查询和统计分析。使用场景包括：复杂的数据统计分析、跨部门的数据共享和协作、多维度的数据查询和分析等。一般情况下，需要设计合适的数据模型和关联规则来确保查询效率和数据准确性。其中包括：

1. 过滤查询：eq
2. 自定义字段查询
3. 查询所有关联数据
4. 条件过滤查询：filter
5. 通过内关联/左关联查询
6. 连接运算符：or
7. 连接运算符：or & and
8. 数据排序：order
9. 过滤数据的条数： count
10. 限制返回的行数： limit

本教程是通过数据表：学生课表信息表、课程信息表、学校信息表，三者之间存在的关联关系的示例来讲解如何通过JavaScript SDK的API进行多表关联查询数据的教程。

准备工作，创建数据表

创建subject表#

subject表主要记录课程信息，表结构字段如下：

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

创建school表#

school表主要记录学校信息，表结构字段如下：

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

创建subject_student表#

subject_student表主要记录学生的课表和学校信息，表结构字段如下：

关键点：

1.创建subject_student表时，必须要设置字段subjectId与subject表的id关联和字段schoolId与school表的id关联，否则将不能执行关联查询。

2.需要创建"studentId"、"subjectId"、"schoolId"联合唯一索引。

1CREATE TABLE "public"."subject_student" ( 
2  "id" BIGINT NOT NULL,
3  "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL,
4  "studentId" BIGINT NOT NULL,
5  "schoolId" BIGINT NULL,
6  "subjectId" BIGINT NOT NULL,
7  CONSTRAINT "subject_student_pkey" PRIMARY KEY ("id")
8);
9--设置subjectId与课程表的id关联和schoolId与学校表的id关联
10ALTER 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;
11ALTER 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;
12-- 创建联合唯一索引
13CREATE UNIQUE INDEX subject_student_schoolId_ukey ON "public"."subject_student"("studentId","schoolId","subjectId");
14
15INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('1', '2022-08-17 09:51:14.733+08', '20222201', '1','1');
16INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('2', '2022-08-17 16:14:51.236+08', '20222202', '2','2');
17INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('3', '2022-08-17 17:29:33.938+08', '20222201', '3','3');
18INSERT INTO "public"."subject_student" ("id", "updated_at", "studentId", "subjectId","schoolId") VALUES ('4', '2022-08-18 17:30:09.934+08', '20222202', '4','4');

1.条件过滤查询：eq#

查询学生的studentId为20222201的课表信息

1  const { data, error } = await supabase
2    .from('subject_student')
3    .select(`
4    subject (id,subjectName,teacherName,adress)
5  `).eq("studentId","20222201")

使用方法注意：

1.在这个查询中，from后面的subject_student是主表，subject是关联表，id,subjectName,teacherName,adress是查询subject中的指定字段，用逗号隔开，用括号包住。

2.eq通常是指"="，也就是等于运算符。它只能过滤主表的字段，不能过滤外联表的字段。

结果：

1[
2  {
3    subject: {
4      id: 1,
5      subjectName: '数据库原理',
6      teacherName: '张珊',
7      adress: '计科楼2-1003'
8    }
9  },
10  {
11    subject: {
12      id: 3,
13      subjectName: '分布式数据库',
14      teacherName: '王五',
15      adress: '4教-101'
16    }
17  }
18]

2.自定义字段查询#

查询学生的studentId为20222201的课表信息和学校信息。

当您想要自定义查询主表与关联表的一些字段时，您可以在select里面用逗号把主表的字段隔开，后面加上想要查询的关联表，关联表紧挨着用括号包裹它的字段，也需要用逗号隔开。

1const { data, error } = await supabaseJs
2    .from('subject_student')
3    .select(`
4    id,
5    studentId,
6    subject (id,subjectName,teacherName,adress),
7    school (id,schoolName)
8  `).eq("studentId","20222201")

结果：

1[
2  {
3    id: 1,
4    studentId: 20222201,
5    subject: {
6      id: 1,
7      subjectName: '数据库原理',
8      teacherName: '张珊',
9      adress: '计科楼2-1003'
10    },
11    school: { id: 1, schoolName: '武汉大学' }
12  },
13  {
14    id: 3,
15    studentId: 20222201,
16    subject: {
17      id: 3,
18      subjectName: '分布式数据库',
19      teacherName: '王五',
20      adress: '4教-101'
21    },
22    school: { id: 3, schoolName: '华中师范大学' }
23  }
24]

3.查询所有关联数据#

查询所有学生的课表信息

*代表查询所在表的所有字段信息。

查询所有关联数据时，select 后面不需要任何条件过滤。

1 const { data, error } = await supabase
2    .from('subject_student')
3    .select(`*,
4    subject (*)
5  `)

结果：

1[
2    {
3        "id": 3,
4        "updated_at": "2022-08-17T09:29:33.938+00:00",
5        "studentId": 20222201,
6        "schoolId": 3,
7        "subjectId": 3,
8        "subject": {
9            "id": 3,
10            "teacherName": "王五",
11            "updated_at": "2022-08-17T09:28:49.603+00:00",
12            "adress": "4教-101",
13            "subjectName": "分布式数据库"
14        }
15    },
16    {
17        "id": 1,
18        "updated_at": "2022-08-17T01:51:14+00:00",
19        "studentId": 20222201,
20        "schoolId": 1,
21        "subjectId": 2,
22        "subject": {
23            "id": 2,
24            "teacherName": "李四",
25            "updated_at": "2022-08-17T08:13:54.527+00:00",
26            "adress": "重楼2-3112",
27            "subjectName": "计算机基础"
28        }
29    },
30    {
31        "id": 2,
32        "updated_at": "2022-08-17T08:14:51+00:00",
33        "studentId": 20222202,
34        "schoolId": 2,
35        "subjectId": 4,
36        "subject": {
37            "id": 4,
38            "teacherName": "王博",
39            "updated_at": "2022-08-18T09:28:59.265+00:00",
40            "adress": "综合楼4-401",
41            "subjectName": "高等数学"
42        }
43    },
44    {
45        "id": 4,
46        "updated_at": "2022-08-18T09:30:09+00:00",
47        "studentId": 20222202,
48        "schoolId": 4,
49        "subjectId": 1,
50        "subject": {
51            "id": 1,
52            "teacherName": "张珊",
53            "updated_at": "2022-08-12T10:28:30.725+00:00",
54            "adress": "计科楼2-1003",
55            "subjectName": "武汉大学"
56        }
57    }
58]

4.条件过滤查询：filter#

小知识：filter 、eq、and和or的区别。

• filter通常指的是 SELECT 语句中的 WHERE 子句。WHERE 子句用于筛选符合指定条件的数据行，它指定了一个或多个条件，以便只返回满足条件的数据。
• OR 是用于连接两个或多个条件，表示只要满足其中任意一个条件即可。
• AND 是用于连接两个或多个条件，表示同时满足所有条件才能返回结果。
• filter是用于筛选满足指定条件的记录，它可以使用一系列操作符（如=，<>，<,>等）来定义多种筛选条件，而eq则是SQL中的一个方法，用于创建filter条件中的=操作符。

查询学生的studentId为20222201的课程名称为“数据库原理”的课表信息和某个学生的学校为“武汉大学”的学校信息。

1    const { data, error } = await supabaseJs
2    .from('subject_student')
3    .select(`
4    school (schoolName),
5    subject (id,subjectName,teacherName,adress)
6  `).filter('school.schoolName', 'eq', '武汉大学').filter('subject.subjectName', 'eq', '数据库原理').filter('studentId','eq','20222201')

使用方法注意：

1.在这个查询中，from后面的subject_student是主表，subject是关联表，id,subjectName,teacherName,adress是查询subject中的指定字段，用逗号隔开，用括号包住。

2.filter是用于筛选满足指定条件的记录，它可以使用一系列操作符（如=，<>，<,>等）来定义多种筛选条件,比如在这个查询中就使用了eq来定义了筛选条件。

3.filter第一个参数是数据表的指定查询字段，如果要查询外联表的字段，则需要带上外联表的名称,例如：subject.subjectName，就是通过subject外联表的subjectName字段来筛选，如果不带则表示通过主表的字段筛选。

结果：

1[
2  {
3    school: { schoolName: '武汉大学' },
4    subject: {
5      id: 1,
6      subjectName: '数据库原理',
7      teacherName: '张珊',
8      adress: '计科楼2-1003'
9    }
10  },
11  { school: null, subject: null }
12]

"null"出现的原因：如果不满足对外部表列的过滤器（在这个例子中外部表是subject和school），则外部表返回[]或null，但不会过滤掉主表。如果要过滤掉主表行，使用!inner。

5.通过内关联/左关联查询#

查询学生的studentId为20222201，课程名称为“高等数学”或者老师是王五的课表信息。

目前暂时只提供!inner和!left两种关联查询。

如果要过滤掉主表行，则可使用!inner。

1    const { data, error } = await supabaseJs
2    .from('subject_student')
3    .select(`
4    subject!inner (id,subjectName,teacherName,adress)
5  `).or('subjectName.eq.高等数学,teacherName.eq.王五', { foreignTable: 'subject' }).eq('studentId','20222201')

结果：

1[
2  {
3    subject: {
4      id: 3,
5      subjectName: '分布式数据库',
6      teacherName: '王五',
7      adress: '4教-101'
8    }
9  }
10]

6.连接运算符：or#

and 和 or 也叫连接运算符，在查询数据时用于缩小查询范围，我们可以用 and 或者 or 指定一个或多个查询条件。

• and 表示一个或者多个条件必须同时成立。
• or 表示多个条件中只需满足其中任意一个即可。

查询学生的studentId为20222202，课程名称为“高等数学”或者老师是王五的课表信息。

用foreignTable指定过滤的外联表，不设置则会判断为过滤主表。

1  const { data, error } = await supabaseJs
2    .from('subject_student')
3    .select(`
4    subject!inner(id,subjectName,teacherName,adress)
5  `).or('subjectName.eq.高等数学,teacherName.eq.王五', { foreignTable: 'subject' }).eq('studentId','20222202')

结果：

1[
2    {
3        "subject": {
4            "id": 4,
5            "subjectName": "高等数学",
6            "teacherName": "王博",
7            "adress": "综合楼4-401"
8        }
9    }
10]

7.连接运算符：or & and#

用foreignTable指定过滤的外联表，不设置则会判断为过滤主表。

查询学生的studentId为20222202，课程名称为“高等数学”或者老师是王五、教室在4教-101的课表信息。

1    const { data, error } = await supabaseJs
2    .from('subject_student')
3    .select(`
4    subject!inner(id,subjectName,teacherName,adress)
5  `).or('subjectName.eq.高等数学,and(teacherName.eq.王五,adress.eq.4教-101)', { foreignTable: 'subject' }).eq('studentId','20222202')

结果：

1[
2    {
3        "subject": {
4            "id": 4,
5            "subjectName": "高等数学",
6            "teacherName": "王博",
7            "adress": "综合楼4-401"
8        }
9    }
10]

8.数据排序：order#

用foreignTable指定过滤的外联表，不设置则会判断为过滤主表。

查按课程名称降序的课程信息。

1  const { data, error } = await supabaseJs
2  .from('subject_student')
3  .select(`
4  subject (id,subjectName,teacherName,adress)
5`).order('subjectName', { foreignTable: 'subject', ascending: false })

结果：

1[
2  {
3    subject: {
4      id: 1,
5      subjectName: '数据库原理',
6      teacherName: '张珊',
7      adress: '计科楼2-1003'
8    }
9  },
10  {
11    subject: {
12      id: 2,
13      subjectName: '计算机基础',
14      teacherName: '李四',
15      adress: '重楼2-3112'
16    }
17  },
18  {
19    subject: {
20      id: 3,
21      subjectName: '分布式数据库',
22      teacherName: '王五',
23      adress: '4教-101'
24    }
25  },
26  {
27    subject: {
28      id: 4,
29      subjectName: '高等数学',
30      teacherName: '王博',
31      adress: '综合楼4-401'
32    }
33  }
34]

9.过滤数据的条数：count#

查询每个学生的课表有多少个。

注意：为了避免外联表也有跟count相同的名字的字段，建议采取其他的字段命名。

1  const { data, error } = await supabaseJs
2  .from('subject_student')
3  .select(`*,
4  subject (count)
5`)

结果：

1  {
2    id: 1,
3    updated_at: '2022-08-17T01:51:14.733+00:00',
4    studentId: 20222201,
5    schoolId: 1,
6    subjectId: 1,
7    subject: { count: 1 }
8  },
9  {
10    id: 2,
11    updated_at: '2022-08-17T08:14:51.236+00:00',
12    studentId: 20222202,
13    schoolId: 2,
14    subjectId: 2,
15    subject: { count: 1 }
16  },
17  {
18    id: 3,
19    updated_at: '2022-08-17T09:29:33.938+00:00',
20    studentId: 20222201,
21    schoolId: 3,
22    subjectId: 3,
23    subject: { count: 1 }
24  },
25  {
26    id: 4,
27    updated_at: '2022-08-18T09:30:09.934+00:00',
28    studentId: 20222202,
29    schoolId: 4,
30    subjectId: 4,
31    subject: { count: 1 }
32  }
33]

10.限制返回的行数：limit#

查询每个学生的课表信息，并且只返回一条课表信息。

用foreignTable指定过滤的外联表，不设置则会判断为过滤主表。

1  const { data, error } = await supabaseJs
2  .from('subject_student')
3  .select(`*,subject (*)`)
4  .limit(1, { foreignTable: 'subject' })

结果：

1[
2    {
3        "id": 3,
4        "updated_at": "2022-08-17T09:29:33.938+00:00",
5        "studentId": 20222201,
6        "schoolId": 3,
7        "subjectId": 3,
8        "subject": {
9            "id": 3,
10            "teacherName": "王五",
11            "updated_at": "2022-08-17T09:28:49.603+00:00",
12            "adress": "4教-101",
13            "subjectName": "分布式数据库"
14        }
15    },
16    {
17        "id": 1,
18        "updated_at": "2022-08-17T01:51:14+00:00",
19        "studentId": 20222201,
20        "schoolId": 1,
21        "subjectId": 2,
22        "subject": {
23            "id": 2,
24            "teacherName": "李四",
25            "updated_at": "2022-08-17T08:13:54.527+00:00",
26            "adress": "重楼2-3112",
27            "subjectName": "计算机基础"
28        }
29    },
30    {
31        "id": 2,
32        "updated_at": "2022-08-17T08:14:51+00:00",
33        "studentId": 20222202,
34        "schoolId": 2,
35        "subjectId": 4,
36        "subject": {
37            "id": 4,
38            "teacherName": "王博",
39            "updated_at": "2022-08-18T09:28:59.265+00:00",
40            "adress": "综合楼4-401",
41            "subjectName": "高等数学"
42        }
43    },
44    {
45        "id": 4,
46        "updated_at": "2022-08-18T09:30:09+00:00",
47        "studentId": 20222202,
48        "schoolId": 4,
49        "subjectId": 1,
50        "subject": {
51            "id": 1,
52            "teacherName": "张珊",
53            "updated_at": "2022-08-12T10:28:30.725+00:00",
54            "adress": "计科楼2-1003",
55            "subjectName": "武汉大学"
56        }
57    }
58]