안녕하세요.
쿼리 작성하는 데 있어서 의문점(?)이 생겨서 글을 올리게 되었습니다.
테이블 부터 설명드리면
1. user: 유저가 회원가입 하면 저장되는 곳 이라고 생각하시면 될 것 같습니다.
2. workflow: 유저가 워크플로우를 작성하면 저장되는 곳이라고 생각하시면 될 것 같습니다.
3. workflow_person: 유저가 워크플로우를 작성할 때 설정한 승인자와 신청자(자기 자신)이 저장되는 곳이라고 생각하시면 될 것 같습니다.
테이블간의 관계는
workflow : workflow_person = 1 : N
user : workflow_person : = 1 : N
SQL Fiddle로 결과를 확인하실 수 있게 준비를 해봤습니다. 혹시 안보이실 경우는 말씀해주세요.
http://www.sqlfiddle.com/#!9/0b7f89/8
부가 설명을 드리면
SELECT 문에 서브쿼리 한 것과, WHERE 절에 서브쿼리 한 조건은 같은 데 next_approver 컬럼에 대해서 다른 결과가 나타나고 있습니다.
두개의 쿼리 중 "LEFT JOIN user AS e"로 시작하는 라인 빼고는 모두 동일합니다.
해당 구문의 역할은 workflow.status = 1(워크플로우가 진행 중인 상태), workflow_person.type = 1(워크플로우를 신청한 유저)를 만족할 경우, 복수의 승인자 중에서 승인을 해야할 유저를 검색하고 있습니다(workflow_person.order로 우선 순위를 정하고 있습니다).
다른 결과가 나오는 이유나 의심스러운 부분이 보이시면, 도움을 주시면 감사하겠습니다!
###
죄송합니다. 역시 설명이 부족해서 다들 이해하시는 데 어려움을 겪고 계시군요...
우선 결과로는 /* Where statement subquery */ 로 작성한 구문이 정답입니다. 요점은 "next_approver" 컬럼입니다.
workflow.status: 워크플로우 자체의 상태를 의미 합니다(1: 신청 중, 2: 거절, 3: 승인 완료)
workflow.type: 워크플로우의 종류를 의미합니다(1: 계약 워크플로우, 2: 수선 워크플로우...등등)
workflow_person.type: 워크플로우의 신청자와 승인자가 저장됩니다(1: 신청자, 2: 승인을 해야하는 유저)
workflow_person.status: 승인자의 상태를 의미합니다(1: 기본 값, 2: 거절, 3: 승인) ... 신청자의 상태는 기본 값을 갖고 있지만 실제로는 사용되지 않습니다.
#워크로드
1. 승인자 중 한 명이라도 거절하는 경우, 워크플로우 상태가 거절로 바뀌게 됩니다.
2. 승인자 전부가 승인을 한 경우 워크플로우의 상태가 승인 완료로 바뀌게 됩니다.
3. 승인자가 A, B, C 세 명이 등록되어 있습니다. 승인을 할 수 있는 순번은 workflow_person.item_order로 조정하고 있습니다. A 승인자의 item_order = 1, B 승인자의 item_order = 3, C 승인자의 item_order = 2 인 경우는 A 승인자가 승인을 하지 않으면 item_order = 2 인 C 승인자는 액션 권한을 갖고 있지 않습니다(B 승인자도 물론 없습니다).
4. 워크플로우의 상태가 신청 중인 경우에만 승인자가 승인 또는 거절을 할 수 있습니다.
5. next_approver을 보여줘야 하는 조건은, 자신이 워크플로우의 신청자인 경우에만 해당합니다.
6. 자신이 워크플로우의 신청자, 자신이 워크플로우의 승인자 이며 승인을 해야하는 순서, 워크플로우의 승인자 이며 승인을 한 워크플로우. 3가지의 경우를 만족하는 데이터만 결과 값으로써 보여줍니다.
작성하신 SELECT 문을 살펴보다가 그냥 "구문의 역할"이라고 설명한 부분을 만족하도록 새로 작성했습니다.
alias가 person_status와 person_type인 두 컬럼의 의미가 잘 파악이 되지 않는데, 아마 승인자에 대한 workflow_person.status / type 으로 생각되는군요. 다만, 이렇다고 가정하면 person_type은 항상 2이기 때문에 결과에서 제외했습니다.
SELECT STRAIGHT_JOIN u2.name AS next_approver , u1.name AS created_by , s.person_status , s.workflow_status , s.workflow_id , s.workflow_no FROM ( SELECT STRAIGHT_JOIN wp1.workflow_id , wp1.user_id AS created_by_id , w.status AS workflow_status , w.no AS workflow_no , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`) INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id LEFT OUTER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2 WHERE wp1.user_id = 6 AND wp1.type = 1 AND w.status = 1 GROUP BY wp1.workflow_id, wp1.user_id, w.status, w.no ) s INNER JOIN user u1 FORCE INDEX FOR JOIN (PRIMARY) ON u1.id = s.created_by_id LEFT OUTER JOIN user u2 FORCE INDEX FOR JOIN (PRIMARY) ON u2.id = s.next_approver_id;
p.s 작성하신 SELECT 문은 25건이 조회되는데.. user_id=6인 유저가 신청한 워크플로우 중 진행 상태인 워크플로우는 총 19개입니다. 따라서 우리가 만드려는 SELECT 문의 결과도 19건이 나와야 합니다.
1. GROUP BY wp1.workflow_id, wp1.user_id, w.status, w.no 성능 문제
느낌보다는 explain을 보시면 됩니다. 사실 MySQL에서는 뒤의 세 컬럼을 GROUP BY에서 빼고 실행해도 같은 결과를 보여줍니다. (다만, 성능도 같고 그런 작성법은 ANSI 규격 외라서 그렇게 적지는 않았어요)
그런데 애초에 저런 그룹핑이 사용되는 이유는 워크플로우 하나의 신청자가 2명 이상이 될 것처럼 생긴 모델 때문입니다.
정말로 워크 플로우 1개의 신청자가 2명 이상이 되는 경우가 있나요? 그렇지 않다면 신청자의 user_id는 workflow 테이블에 있어야 합니다. 그리고 workflow_person 테이블은 복수의 승인자만 들어가게 하면 되겠죠. 이것만으로도 지금 작성하는 SELECT 문이 많이 단순해 질 수 있을 겁니다.
2. 내가 현재 승인자로 할당되어 있고, 내가 승인할 순서인 워크 플로우는.. 별도의 SELECT 문으로 작성해서 UNION 해 주는 것도 고려해 보세요. (내가 신청하고 내가 승인할 차례인 경우는 양쪽 집합에서 나올테니 이런 중복을 제거하기위해서 부득이 UNION ALL이 아닌 UNION 사용)
답변 감사합니다.
1번에 대해서 말씀 드리면, 워크플로우의 신청자는 1명 뿐입니다.
workflow_person에 신청자를 저장하고 있는 이유는 데이터를 보여줄 때 최근 순으로 등록 또는 승인자로써 지정 받은 워크플로우를 보여줘야 합니다. 그러므로 workflow와 workflow_person를 sorting 하는 것 보다 workflow_person만 sorting 하는 것이 빨랐기 때문에 이렇게 설계를 했습니다.
2번에 대해서 말씀드리면, 어플리케이션 쪽에서 반복문을 이용하여 워크플로우의 승인자인 경우 다음 승인자를 검색하는 SELECT를 하시라는 말씀이신거죠? UNION은 어떤 용도로 쓰이려는 지 잘 모르겠네요...
ANSI 규격에 대해서는 무지하기 때문에 공부 하도록 하겠습니다!
정렬 성능을 말씀하셨는데.. 신청자가 1명이라면 제가 권해드린 것처럼, 신청자의 user_id는 workflow 테이블에 있어야 합니다. 전체적으로는 득보다 실이 클 듯 하네요.
작성하신 쿼리의 결과에는 workflow.status가 1이 아닌 것도 포함되어 있는데 (2, 4, 6, 1340, 1341 번), 본문에서는 이런 워크플로우는 제외하고 싶다고 하셔서 필터링했습니다. -> 그래서 결과는 20개
어느쪽 성능이 좋은지는 테스트 해보시면 되겠지만, 그보다도 모델의 기능성 컬럼이 필요해 보이네요.
특히.. 다수의 승인자 중 현재 승인할 차례인지 아닌지에 대한 flag 컬럼은 꼭 있어야 할 듯 합니다.
지금처럼 SELECT 할 때마다 실시간으로 줄세우는 건 문제가 있어 보입니다.
아래 쿼리에서 UNION을 기준으로.. 윗쪽은 6번 유저가 신청한 워크플로우 / 아랫쪽은 6번 유저가 승인할 차례인 워크플로우입니다. 두 가지 집합은 결과를 얻기까지의 조회 경로가 다르기 때문에 억지로 합치기 보다는 각각을 UNION 하는게 나아 보입니다.
SET @user_id = 6; SELECT STRAIGHT_JOIN u2.name AS next_approver , u1.name AS created_by , s.person_status , s.person_type , s.workflow_status , s.workflow_id , s.workflow_no FROM ( SELECT STRAIGHT_JOIN wp1.workflow_id , wp1.user_id AS created_by_id , w.status AS workflow_status , w.no AS workflow_no , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status , 1 AS person_type FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`) INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id INNER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2 AND wp2.status = 1 WHERE wp1.user_id = @user_id AND wp1.type = 1 AND w.status = 1 GROUP BY wp1.workflow_id UNION SELECT STRAIGHT_JOIN wp1.workflow_id , wp1.user_id AS created_by_id , w.status AS workflow_status , w.no AS workflow_no , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status , 2 AS person_type FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`) INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id INNER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2 AND wp2.status = 1 WHERE wp1.user_id = @user_id AND wp1.type = 2 AND wp1.status = 1 AND w.status = 1 GROUP BY wp1.workflow_id HAVING CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) = @user_id ) s INNER JOIN user u1 FORCE INDEX FOR JOIN (PRIMARY) ON u1.id = s.created_by_id LEFT OUTER JOIN user u2 FORCE INDEX FOR JOIN (PRIMARY) ON u2.id = s.next_approver_id;
답변 감사합니다.
표준에 어긋나는 부분이 구체적으로 어디를 말씀하시는 건가요?
조건문에 대해서 설명해드리면
WHERE절의 IF(a.type = 2, IF(a.item_order = 1, a.id = b.id, IF(a.type = 2 AND b.item_order = a.item_order-1 AND b.status = 3, 1, 0)), a.id = b.id) 은
(1) a.type = 2, 워크플로우의 승인자인 경우에 참입니다.
(2) a.item_order = 1, 순번이 1인 경우(승인을 해야하는 순서)에 참입니다.
(3) (2)를 만족할 경우
a.id = b.id, 즉 워크플로우의 승인자로 지정받았으며 자신이 승인을 해야하는 순번이므로 ID끼리 비교합니다.
(4) (2)를 만족하지 않을 경우
IF(a.type = 2 AND b.item_order = a.item_order-1 AND b.status = 3), 워크플로우의 승인자이고 순번이 1이 아닌 경우에는 조금 복잡합니다... 자신의 순번이 3인 경우, 순번이 2인 승인자의 상태를 보고 자신이 승인을 해야하는 순서인지 아닌지를 판단합니다. 순번이 2인 승인자가 승인을 했을 경우에만 승인을 할 수 있습니다.
(5) (1)을 만족하지 않는 경우, 자신이 신청자이므로 ID끼리 비교합니다.
SELECT e.name AS next_approver , d.name AS created_by , a.status AS person_status , a.type AS person_type , c.status AS workflow_status , c.id AS workflow_id , c.no AS workflow_no FROM workflow_person a -- 본인 INNER JOIN workflow c ON c.id = a.workflow_id INNER JOIN workflow_person b0 -- 작성자 ON b0.workflow_id = a.workflow_id AND b0.type = 1 LEFT OUTER JOIN workflow_person b1 -- 이전승인자 ON b1.workflow_id = a.workflow_id AND b1.type = a.type AND b1.item_order = a.item_order - 1 LEFT OUTER JOIN workflow_person b2 -- 다음승인자 ON b2.workflow_id = a.workflow_id AND b2.type = CASE a.type WHEN 1 THEN 2 ELSE a.type END AND b2.item_order = CASE a.type WHEN 1 THEN 1 ELSE a.item_order + 1 END INNER JOIN user d ON d.id = b0.user_id LEFT OUTER JOIN user e ON e.id = b2.user_id WHERE a.user_id = 6 AND a.status != 3 AND IFNULL(b1.status, 3) = 3 -- 이전승인완료 ;