가입 시 특정 사용자에 대한 문서를 표시하는 SQL 조회

가입 시 특정 사용자에 대한 문서를 표시하는 SQL 조회

조직이 사용자에게 "이 X 문서를 업로드해야 합니다"라고 알려주는 문서 업로드 시스템을 만들었습니다.그러면 사용자는 이메일을 수신하고 해당 조직의 프로파일 페이지로 이동하여 각 문서를 해당 업로드 양식에 업로드할 수 있습니다.이걸 위해 3개의 테이블을 만들었는데vrm_document(조직에 의해 문서 시스템에 업로드된 모든 문서가 보관됩니다). vrm_document_user_link(조직에서 사용자에게 링크되어 있는 문서를 나타냅니다).그리고.vrm_document_user_upload(사용자가 조직에 업로드한 문서가 저장됩니다).

사용자가 업로드해야 하는 모든 문서를 이미 업로드되었는지 여부와 함께 표시할 수 있는 쿼리 권한을 얻을 수 없습니다.

반환되는 불량 데이터에 대한 쿼리를 보여주는 DBFiddle을 만들었습니다.DB Fielle의 예제 출력에서 첫 번째 행은 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

DB Fielle 링크: https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/0

JOIN 문에 user_ids를 포함하도록 DB fielen을 업데이트합니다.https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/2

가입 조건에 사용자 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

언급URL : https://stackoverflow.com/questions/61095120/sql-query-to-show-documents-for-specific-users-when-joined