mysql - 加入时显示特定用户的文档的 SQL 查询

Question

我创建了一个文档上传系统，组织会在其中告诉用户“您需要上传这些 X 文档”。然后用户会收到一封电子邮件，并可以转到该组织的个人资料页面，并以相应的上传表单上传这些文档中的每一个。
我为此创建了 3 个表，vrm_document (这包含由组织上传到文件系统的所有文件)。 vrm_document_user_link (这显示了从组织链接到用户的文档)。和 vrm_document_user_upload (这保存了用户上传到组织的文件)。

我无法让我的查询正确显示用户需要上传的所有文档以及是否已上传的值。

我创建了一个 DBFiddle，显示带有返回的错误数据的查询。在 DB Fiddle 的示例输出中，第一行返回 vrm_document_user_upload_id 和 document_path 的值，而这些是另一个用户的值，但由于 vrm_document_id 匹配，因此它在此处显示这些值。

我如何解决这个查询？

这是我创建的数据库结构，连同插入:

CREATE TABLE vrm_document(
    `vrm_document_id` INT(11) NOT NULL AUTO_INCREMENT,
    `parent_vrm_document_id` INT(11) DEFAULT NULL,
    `is_default_document` TINYINT(3) DEFAULT '0',
    `country_id` INT(11) DEFAULT NULL COMMENT 'used only if document is a default document',
    `user_id` INT(11) DEFAULT NULL COMMENT 'user id of who it is created for, null for default documents',
    `user_auth_level` INT(11) DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `version_name` VARCHAR(255) DEFAULT NULL,
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'empty for certain document types',
    `vrm_document_type_id` INT(11) NOT NULL,
    `document_preview` VARCHAR(255) NULL,
    `document_preview_thumbnail` VARCHAR(255) NULL,
    `document_path` VARCHAR(255) DEFAULT NULL,
    `language_id` INT(11) DEFAULT NULL,
    `timestamp_created_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `timestamp_modified_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `create_user_id` INT(11) DEFAULT NULL COMMENT 'the user id of who created the document',
    `create_user_auth_level` INT(11) DEFAULT NULL,
    `create_user_id_toggle` INT(11) DEFAULT NULL COMMENT 'user id of who was toggled to to create the document',
    `create_user_auth_level_toggle` INT(11) DEFAULT NULL,
    PRIMARY KEY (`vrm_document_id`)
) ENGINE = InnoDB;

CREATE TABLE vrm_document_user_upload(
    `vrm_document_user_upload_id` INT(11) NOT NULL AUTO_INCREMENT,
    `from_user_id` INT(11) NOT NULL,
    `from_user_auth_level` INT(11) NOT NULL,
    `to_user_id` INT(11) NOT NULL,
    `to_user_auth_level` INT(11) NOT NULL,
    `vrm_document_id` INT(11) NOT NULL,
    `document_path` VARCHAR(255) NOT NULL,
    `vrm_document_upload_status_id` INT(11) NOT NULL DEFAULT 0,
    `reject_reason` VARCHAR(255) DEFAULT NULL,
    `timestamp_uploaded_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `create_user_id` INT(11) DEFAULT NULL COMMENT 'the user id of who uploaded the document',
    `create_user_auth_level` INT(11) DEFAULT NULL,
    `create_user_id_toggle` INT(11) DEFAULT NULL COMMENT 'user id of who was toggled to to upload the document',
    `create_user_auth_level_toggle` INT(11) DEFAULT NULL,
    PRIMARY KEY (`vrm_document_user_upload_id`)
) ENGINE = InnoDB;

CREATE TABLE vrm_document_user_link(
    `from_user_id` INT(11) NOT NULL COMMENT 'the user id who attached the document to another user',
    `to_user_id` INT(11) NOT NULL COMMENT 'the user id who the document is attached to',
    `vrm_document_id` INT(11) NOT NULL,
    FOREIGN KEY (`vrm_document_id`) REFERENCES `vrm_document` (`vrm_document_id`)
) ENGINE = InnoDB;


INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (1, NULL, 1, 19, NULL, NULL, 'Vrijwilligerscontract', 'Vrijwilligerscontract - Give a Day', 'Standaard vrijwilligerscontract aangeboden door Give a Day.', 2, NULL, NULL, '/vrm/documents/default_documents/180130_Uitnodiging_Aventi-in-beweging_v2.pdf', 14, '2020-03-27 14:53:19', '2020-03-27 14:53:19', NULL, NULL, NULL, NULL);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (9, NULL, 0, NULL, 2, 5, 'Nieuw doc als test', 'v1', 'Dit is een test', 2, NULL, NULL, 'vrm/documents/uploaded_documents/2020/03/template for evaluation of KBC Minimal Data Security requirements v2.0-DRAFT1-27032020163601.docx', 14, '2020-03-27 17:36:01', '2020-03-27 17:36:01', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (10, NULL, 0, NULL, 2, 5, 'Attest goed gedrag en zeden', '', 'Het attest van gedrag en goede zeden moet opgevraagd worden en terug opgeladen worden voor elke vrijwilliger die start bij ons. ', 3, NULL, NULL, NULL, 14, '2020-03-27 18:40:42', '2020-03-27 18:40:42', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (12, NULL, 0, NULL, 2, 5, 'test type 3', '', 'test voor type 3', 3, NULL, NULL, NULL, 14, '2020-03-31 07:19:14', '2020-03-31 07:19:14', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (64, NULL, 0, NULL, 2, 5, 'ooooooooo111', '', 'aezfs<wvcxcvw', 3, NULL, NULL, NULL, 14, '2020-04-03 12:21:06', '2020-04-03 12:21:06', 1, 1, 2, 5);



INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 1);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 9);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 10);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 12);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 64);


INSERT INTO `vrm_document_user_upload` (`vrm_document_user_upload_id`, `from_user_id`, `from_user_auth_level`, `to_user_id`, `to_user_auth_level`, `vrm_document_id`, `document_path`, `vrm_document_upload_status_id`, `reject_reason`, `timestamp_uploaded_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (5, 1, 1, 2, 5, 1, 'vrm/documents/user_uploaded_documents/2020/04/helpende-handen-werf-44-01042020125653-07042020162326.docx', 1, NULL, '2020-04-07 16:23:26', 1, 1, NULL, NULL);

这是我试图给我正确结果的查询:

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu ON vdul.vrm_document_id = vduu.vrm_document_id
LEFT JOIN vrm_document AS vd ON vdul.vrm_document_id = vd.vrm_document_id
WHERE vdul.from_user_id = 2
AND vdul.to_user_id = 24
AND vd.vrm_document_type_id != 1

在 Balmar 响应后更新第二个查询:

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu ON ((vdul.vrm_document_id = vduu.vrm_document_id) AND (vdul.from_user_id = 2 AND vdul.to_user_id = 24))
LEFT JOIN vrm_document AS vd ON vdul.vrm_document_id = vd.vrm_document_id
WHERE vd.vrm_document_type_id != 1

数据库 fiddle 链接:
https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/0

更新 DB fiddle 以在 JOIN 语句中包含 user_ids:
https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/2

Answer 1

您需要将用户 ID 添加到加入条件中，因此您只能获取相同用户的文档。

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu 
    ON vdul.vrm_document_id = vduu.vrm_document_id 
        AND vdul.to_user_id = vduu.to_user_id 
        AND vdul.from_user_id = vduu.from_user_id
LEFT JOIN vrm_document AS vd 
    ON vdul.vrm_document_id = vd.vrm_document_id 
        AND vd.vrm_document_type_id != 1
WHERE vdul.from_user_id = 2
AND vdul.to_user_id = 24