关联关系查询

前言

当前场景下，关联关系的识别主要靠 MySQL 外键约束来实现，外键字段所在的表称为 主表 是关联关系中的「多方」，比如多对一场景中，关联关系由多方的外键字段来保存关系，被关联的表称为 从表。

使用关联查询时遵循以下规则：

模式归纳表

• 按照关联用法

用法	表达式	示例
通过关联字段联表查询	/table?select=字段1,字段2,关联字段(子字段1,子字段2)	/film?select=title,release_year,duration,director_id(name,country)
指定关联字段别名并联表查询	/table?select=字段1,字段2,别名:关联字段(子字段1,子字段2)	/film?select=title,release_year,duration,d:director_id(name,country)
通过表名联表查询	/table?select=字段1,字段2,关联表(子字段1,子字段2)	/film?select=title,release_year,duration,director(name,country)
指定表名别名并联表查询	/table?select=字段1,字段2,别名:关联表(子字段1,子字段2)	/film?select=title,release_year,duration,d:director(name,country)
通过外键联表查询	/table?select=字段1,字段2,外键(子字段1,子字段2)	/film?select=title,release_year,duration,fk_relate_director(name,country)
指定外键别名并联表查询	/table?select=字段1,字段2,别名:外键(子字段1,子字段2)	/film?select=title,release_year,duration,d:fk_relate_director(name,country)
自关联时，同表多字段必须指定别名(如企业员工对应有上级和下级)	/table?select=字段1,字段2,别名1:表名(子字段1,子字段2),别名2:表名(子字段1，子字段2)	/staff?select=name,position,employee:staff(name,position),employer:staff(name,position)

• 按照联表方式

用法	表达式	示例	sql示例
外关联	/table?select=字段1,字段2,关联字段(子字段1,子字段2)	/film?select=title,release_year,duration,director_id(name,country)	select title, release_year, duration from film as t0 left join director as t1 on t0.director_id = t1.id
内关联	/table?select=字段1,字段2,关联字段!inner(子字段1,子字段2)	/film?select=title,release_year,duration,director_id!inner(name,country)	select title, release_year, duration from film as t0 inner join director as t1 on t0.director_id = t1.id

多对一

表和E-R

-- 导演表（一）
CREATE TABLE `director` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL COMMENT '导演姓名',
  `country` VARCHAR(30) COMMENT '国籍',
  `_openid` VARCHAR(64) COMMENT '权限字段'
) ENGINE=InnoDB COMMENT='导演表';

-- 电影表（多）
CREATE TABLE `film` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称',
  `release_year` YEAR COMMENT '发行年份',
  `director_id` INT COMMENT '导演ID',
  `duration` INT COMMENT '片长(分钟)',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  CONSTRAINT `fk_film_director_id` FOREIGN KEY (`director_id`)
    REFERENCES `director` (`id`)
) ENGINE=InnoDB COMMENT='电影表';

准备数据

-- 插入5位导演
INSERT INTO `director` (`id`, `name`, `country`, `_openid`) VALUES
(1, 'Steven Spielberg', 'USA', '1977683311217119233'),
(2, 'Christopher Nolan', 'UK', '1977683311217119233'),
(3, 'Hayao Miyazaki', 'Japan', '1977683311217119233'),
(4, 'Quentin Tarantino', 'USA', '1977683311217119233'),
(5, 'Martin Scorsese', 'USA', '1977683311217119233');

-- 插入5部电影，其中3部指定导演ID
INSERT INTO `film` (`id`, `title`, `release_year`, `director_id`, `duration`, `_openid`) VALUES
(1, 'Jurassic Park', 1993, 1, 164, '1977683311217119233'), 
(2, 'Inception', 2010, 2, 172, '1977683311217119233'),     
(3, 'Spirited Away', 2001, 3, 98, '1977683311217119233'); 

-- 插入2部未知导演的电影
INSERT INTO `film` (`id`, `title`, `release_year`, `director_id`, `duration`, `_openid`) VALUES
(4, 'Unknown Film 1', 2020, NULL, 100, '1977683311217119233'),
(5, 'Untitled Project', 2015, NULL, 100, '1977683311217119233');

查询请求

查询电影表，获取电影名称、发行年份、导演姓名、国籍

Request

curl -i -X GET 'http://{{host}}/v1/rdb/rest/v1/film?select=id,title,release_year,duration,director_id(id,name,country)' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

Response

HTTP/1.1 200 OK

Header:
    content-length: 573
    content-type: application/json; charset=utf-8

Body:
[
    {
        "director_id": {
            "country": "USA",
            "id": 1,
            "name": "Steven Spielberg"
        },
        "duration": 164,
        "id": 1,
        "release_year": 1993,
        "title": "Jurassic Park"
    },
    {
        "director_id": {
            "country": "UK",
            "id": 2,
            "name": "Christopher Nolan"
        },
        "duration": 172,
        "id": 2,
        "release_year": 2010,
        "title": "Inception"
    },
    {
        "director_id": {
            "country": "Japan",
            "id": 3,
            "name": "Hayao Miyazaki"
        },
        "duration": 98,
        "id": 3,
        "release_year": 2001,
        "title": "Spirited Away"
    },
    {
        "director_id": null,
        "duration": 100,
        "id": 4,
        "release_year": 2020,
        "title": "Unknown Film 1"
    },
    {
        "director_id": null,
        "duration": 100,
        "id": 5,
        "release_year": 2015,
        "title": "Untitled Project"
    }
]

一对多

表和E-R

-- 电影表（一）
CREATE TABLE `film` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称',
  `release_year` YEAR COMMENT '发行年份',
  `duration` INT COMMENT '片长(分钟)',
  `_openid` VARCHAR(64) COMMENT '权限字段'
) ENGINE=InnoDB COMMENT='电影表';

-- 影评表（多）
CREATE TABLE `review` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `content` TEXT COMMENT '评论内容',
  `film_id` INT NOT NULL COMMENT '关联电影ID',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  CONSTRAINT `fk_review_film_id` FOREIGN KEY (`film_id`) 
    REFERENCES `film` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='影评表';

数据准备

-- 插入3部电影（明确指定ID）
INSERT INTO `film` (`id`, `title`, `release_year`, `duration`, `_openid`) VALUES
(1, 'The Shawshank Redemption', 1994, 142, '1977683311217119233'),
(2, 'The Godfather', 1972, 173, '1977683311217119233'),
(3, 'Pulp Fiction', 1994, 98, '1977683311217119233');

-- 为《The Shawshank Redemption》添加2条影评
INSERT INTO `review` (`id`, `content`, `film_id`, `_openid`) VALUES
(1, 'One of the greatest movies ever made!', 1, '1977683311217119233'),
(2, 'Tim Robbins and Morgan Freeman delivered outstanding performances.', 1, '1977683311217119233');

-- 为《The Godfather》添加1条影评
INSERT INTO `review` (`id`, `content`, `film_id`, `_openid`) VALUES
(3, 'Marlon Brando\'s performance is legendary.', 2, '1977683311217119233');

-- 《Pulp Fiction》没有影评

查询请求

Request

curl -i -X 'GET http://{{host}}/v1/rdb/rest/film?select=id,title,release_year,duration,review(content)' \
-H 'Content-Type: application/json' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \

Response

HTTP/1.1 200 OK

Header:
    content-length: 437
    content-type: application/json; charset=utf-8
    
Body:
[
    {
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "review": [
            {
                "content": "One of the greatest movies ever made!"
            },
            {
                "content": "Tim Robbins and Morgan Freeman delivered outstanding performances."
            }
        ],
        "title": "The Shawshank Redemption"
    },
    {
        "duration": 173,
        "id": 2,
        "release_year": 1972,
        "review": [
            {
                "content": "Marlon Brando's performance is legendary."
            }
        ],
        "title": "The Godfather"
    },
    {
        "duration": 98,
        "id": 3,
        "release_year": 1994,
        "review": [

        ],
        "title": "Pulp Fiction"
    }
]

一对一

表和E-R

-- 电影表（一）
CREATE TABLE `film` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称'
  `release_year` YEAR COMMENT '发行年份',
  `duration` INT COMMENT '时长（分钟）',
  `_openid` VARCHAR(64) COMMENT '权限字段'
) ENGINE=InnoDB COMMENT='电影表';

-- 获奖信息表（一）
CREATE TABLE `award` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `award_name` VARCHAR(100) COMMENT '获奖名称',
  `year` YEAR COMMENT '获奖年份',
  `film_id` INT COMMENT '关联电影ID',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  UNIQUE KEY `uk_award_film_id` (`film_id`),
  CONSTRAINT `fk_award_film_id` FOREIGN KEY (`film_id`) 
    REFERENCES `film` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='获奖信息表';

数据准备

-- 插入3部电影
INSERT INTO `film` (`id`, `title`, `release_year`, `duration`, `_openid`) VALUES
(1, 'The Shawshank Redemption', 1994, 142, '1977683311217119233'),
(2, 'The Godfather', 1972, 175, '1977683311217119233'),
(3, 'Pulp Fiction', 1994, 154, '1977683311217119233');

-- 插入2条获奖信息（演示一对一关系中"零关联"的情况）
INSERT INTO `award` (`id`, `award_name`, `year`, `film_id`, `_openid`) VALUES
(1, 'Best Picture Nominee', 1994, 1, '1977683311217119233'),
(2, 'Academy Award for Best Picture', 1972, 2, '1977683311217119233');

查询请求

Request

curl -i -X 'GET http://{{host}}/v1/rdb/rest/award?select=id,title,release_year,duration,award(award_name,year)' \
-H 'Authorization: Bearer <This is a fake token, please use yours>' \

Response

HTTP/1.1 200 OK

Header:
    content-length: 354
    content-type: application/json; charset=utf-8

Body:
[
    {
        "award": {
            "award_name": "Best Picture Nominee",
            "year": 1994
        },
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    },
    {
        "award": {
            "award_name": "Academy Award for Best Picture",
            "year": 1972
        },
        "duration": 175,
        "id": 2,
        "release_year": 1972,
        "title": "The Godfather"
    },
    {
        "award": null,
        "duration": 154,
        "id": 3,
        "release_year": 1994,
        "title": "Pulp Fiction"
    }
]

多对多

表和E-R

-- 演员表
CREATE TABLE `actor` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL COMMENT '演员姓名',
  `country` VARCHAR(50) COMMENT '国籍',
  `_openid` VARCHAR(64) COMMENT '权限字段'
) ENGINE=InnoDB COMMENT='演员表';

-- 电影表
CREATE TABLE `film` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL COMMENT '电影名称',
  `release_year` YEAR COMMENT '发行年份',
  `duration` INT COMMENT '时长（分钟）',
  `_openid` VARCHAR(64) COMMENT '权限字段'
) ENGINE=InnoDB COMMENT='电影表';

-- 中间表（电影-演员参演关系）
CREATE TABLE `film_actor` (
  `id` INT AUTO_INCREMENT,
  `film_id` INT NOT NULL,
  `actor_id` INT NOT NULL,
  `role_name` VARCHAR(50) COMMENT '饰演角色',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  PRIMARY KEY (`id`, `film_id`, `actor_id`), -- 中间表外键作为联合主键一部分，才能识别多对多关系，`PRIMARY KEY (`film_id`, `actor_id`)` 也符合
  CONSTRAINT `fk_fa_film_id` FOREIGN KEY (`film_id`) REFERENCES `film` (`id`),
  CONSTRAINT `fk_fa_actor_id` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`id`)
) ENGINE=InnoDB COMMENT='电影演员关联表';

使用说明

• 多对多关系识别，需要使用联合主键，联合主键的字段需要包含两个外键，且作为联合主键的一部分，才能识别多对多关系。

• 请求中不需要特别指定中间表，系统会根据上述规则自动识别

数据准备

-- 插入3部电影数据
INSERT INTO `film` (`id`, `title`, `release_year`, `duration`, `_openid`) VALUES
(1, 'The Shawshank Redemption', 1994, 142, '1977683311217119233'), 
(2, 'Titanic', 1997, 195, '1977683311217119233'),                  
(3, 'The Godfather', 1972, 175, '1977683311217119233');      

-- 插入5位演员数据
INSERT INTO `actor` (`id`, `name`, `country`, `_openid`) VALUES
(1, 'Tom Hanks', 'USA', '1977683311217119233'), 
(2, 'Meryl Streep', 'USA', '1977683311217119233'),
(3, 'Leonardo DiCaprio', 'USA', '1977683311217119233'), 
(4, 'Kate Winslet', 'UK', '1977683311217119233'),     
(5, 'Morgan Freeman', 'USA', '1977683311217119233');  

-- 建立关联关系（电影3没有演员关联）
INSERT INTO `film_actor` (`film_id`, `actor_id`, `role_name`, `_openid`) VALUES
(1, 5, 'Ellis Boyd "Red" Redding', '1977683311217119233'),
(2, 3, 'Jack Dawson', '1977683311217119233'),
(2, 4, 'Rose DeWitt Bukater', '1977683311217119233');

查询请求

Request

curl -i -X 'GET http://{{host}}/v1/rdb/rest/award?select=id,title,release_year,duration,actor(name,country)' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

Response

HTTP/1.1 200 OK

Header:
    content-length: 367
    content-type: application/json; charset=utf-8

Body:
[
    {
        "actor": [
            {
                "country": "USA",
                "name": "Morgan Freeman"
            }
        ],
        "duration": 142,
        "id": 1,
        "release_year": 1994,
        "title": "The Shawshank Redemption"
    },
    {
        "actor": [
            {
                "country": "USA",
                "name": "Leonardo DiCaprio"
            },
            {
                "country": "UK",
                "name": "Kate Winslet"
            }
        ],
        "duration": 195,
        "id": 2,
        "release_year": 1997,
        "title": "Titanic"
    },
    {
        "actor": [

        ],
        "duration": 175,
        "id": 3,
        "release_year": 1972,
        "title": "The Godfather"
    }
]

自关联

表和E-R

-- 员工表
CREATE TABLE `employee` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL COMMENT '员工姓名',
  `position` VARCHAR(100) COMMENT '职位',
  `manager_id` INT COMMENT '直属经理ID',
  `mentor_id` INT COMMENT '导师ID',
  `_openid` VARCHAR(64) COMMENT '权限字段',
  CONSTRAINT `fk_employee_manager_id` FOREIGN KEY (`manager_id`) 
    REFERENCES `employee` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employee_mentor_id` FOREIGN KEY (`mentor_id`) 
    REFERENCES `employee` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB COMMENT='员工表';

数据准备

-- 插入员工数据
INSERT INTO `employee` (`id`, `name`, `position`, `manager_id`, `mentor_id`, `_openid`) VALUES
(1, 'John Smith', 'CEO', NULL, NULL, '1977683311217119233'),
(2, 'Sarah Johnson', 'CTO', NULL, NULL, '1977683311217119233'),
-- 部门经理（向CEO汇报，导师为CTO）
(3, 'Michael Brown', 'Engineering Manager', 1, 2, '1977683311217119233'),
(4, 'Emily Davis', 'Marketing Manager', 1, 2, '1977683311217119233'),
-- 部门经理（向CTO汇报，导师为CEO）
(5, 'Robert Wilson', 'Product Manager', 2, 1, '1977683311217119233'),

-- 普通员工
-- 经理和导师都是Michael
(6, 'Lisa Anderson', 'Senior Developer', 3, 3, '1977683311217119233'),  
-- 经理Michael，导师Lisa
(7, 'David Lee', 'Junior Developer', 3, 6, '1977683311217119233');

查询请求

Request

curl -i -X 'GET http://{{host}}/v1/rdb/rest/empoyee?select=name,position,manager:manager_id(name,position),mentor:mentor_id(name,position)' \
-H 'Authorization: Bearer <This is a fake token, please use yours>'

Response

HTTP/1.1 200 OK

Header:
    content-length: 979
    content-type: application/json; charset=utf-8

Body:
[
    {
        "manager": null,
        "mentor": null,
        "name": "John Smith",
        "position": "CEO"
    },
    {
        "manager": null,
        "mentor": null,
        "name": "Sarah Johnson",
        "position": "CTO"
    },
    {
        "manager": {
            "name": "John Smith",
            "position": "CEO"
        },
        "mentor": {
            "name": "Sarah Johnson",
            "position": "CTO"
        },
        "name": "Michael Brown",
        "position": "Engineering Manager"
    },
    {
        "manager": {
            "name": "John Smith",
            "position": "CEO"
        },
        "mentor": {
            "name": "Sarah Johnson",
            "position": "CTO"
        },
        "name": "Emily Davis",
        "position": "Marketing Manager"
    },
    {
        "manager": {
            "name": "Sarah Johnson",
            "position": "CTO"
        },
        "mentor": {
            "name": "John Smith",
            "position": "CEO"
        },
        "name": "Robert Wilson",
        "position": "Product Manager"
    },
    {
        "manager": {
            "name": "Michael Brown",
            "position": "Engineering Manager"
        },
        "mentor": {
            "name": "Michael Brown",
            "position": "Engineering Manager"
        },
        "name": "Lisa Anderson",
        "position": "Senior Developer"
    },
    {
        "manager": {
            "name": "Michael Brown",
            "position": "Engineering Manager"
        },
        "mentor": {
            "name": "Lisa Anderson",
            "position": "Senior Developer"
        },
        "name": "David Lee",
        "position": "Junior Developer"
    }
]