[필기노트] MySQL VIEW, API

-- GET /api/search/room

1. 모든 Posts를 출력

SELECT * FROM Posts;

2. Posts에서 해당하는 인자만 출력

SELECT postId, title, postImg, maxMember, startDate, endDate, place
FROM Posts
;

3. currentMember 추가
SELECT p.postId, p.title, p.postImg, p.content, COUNT(c.userId) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
GROUP BY c.postId
;

4. tag 추가
SELECT p.postId, p.title, p.postImg, p.content, COUNT(c.userId) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
    (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem  
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
GROUP BY c.postId
;


5. userId가 참가중인 Channels의 리스트를 출력

SELECT * FROM Channels where userId = 2;


6. WHERE 절 추가

SELECT p.postId, p.title, p.postImg, p.content, COUNT(c.userId) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
    (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem  
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE c.postId IN (SELECT DISTINCT postId FROM Channels WHERE userId = 2)
GROUP BY c.postId
;

7. LIKE 추가

SELECT p.postId, p.title, p.postImg, p.content, COUNT(c.userId) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
    (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem  
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE c.postId IN (SELECT DISTINCT postId FROM Channels WHERE userId = 2)
    AND p.title LIKE '%G%'
GROUP BY c.postId
;

8. ORDER BY 및 LIMIT 추가
SELECT p.postId, p.title, p.postImg, p.content, COUNT(c.userId) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
    (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem  
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE c.postId IN (SELECT DISTINCT postId FROM Channels WHERE userId = 2)
    AND p.title LIKE '%G%'
GROUP BY c.postId
ORDER BY p.postId ASC
LIMIT 0, 5
;


-- GET /api/post/posts/invite 초대된 리스트 출력 

1. Invites 테이블 조회

SELECT inviteId, giveUserId, postId FROM Invites;

2. 해당하는 receiveUserId 만 출력하도록 설정

SELECT inviteId, giveUserId, postId FROM Invites WHERE receiveUserId = 5;

3. Posts 테이블을 JOIN

SELECT i.InviteId, i.giveUserId, i.postId, p.title, p.postImg, p.maxMember, p.startDate, p.endDate, p.place
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
WHERE receiveUserId = 5
;

4. currentMember 적용해보기
SELECT COUNT(c.userId) AS currentMember, p.maxMember
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
GROUP BY c.postId
;


5. GROUP BY currentMember 추가 (실패)

SELECT p.postId, p.title, p.postImg, COUNT(*) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
JOIN Channels AS c
ON p.postId = c.postId
WHERE i.receiveUserId = 5
GROUP BY c.postId
;
 - Invites의 데이터를 출력하려고 하니 에러가 발생한다.
 - currentMember를 GROUP BY로 전환하자


5-1. Sub Query currentMember 추가 (성공)

SELECT i.InviteId, i.giveUserId AS userId, i.postId, p.title, p.postImg, 
    (SELECT COUNT(userId) FROM Channels WHERE postId = p.postId) AS currentMember,
    p.maxMember, p.startDate, p.endDate, p.place
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
WHERE i.receiveUserId = 3
;
 - Sub Query에서 GROUP BY를 사용하지않고 조회가 가능하다
 - 시간 복잡도는 GROUP BY보다 Sub Query가 더 우월한 것 같다.
 - Channels를 필수로 사용하지 않는 Query의 currentMember를 전부 수정하자. ☆



6. ORDER BY, LIMIT 추가 

SELECT i.InviteId, i.giveUserId AS userId, i.postId, p.title, p.postImg, 
    (SELECT COUNT(userId) FROM Channels WHERE postId = p.postId) AS currentMember,
    p.maxMember, p.startDate, p.endDate, p.place
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
WHERE i.receiveUserId = 3
ORDER BY i.postId
LIMIT 0, 5
;


7. 초대를 보낸 사용자의 nickname, profileImg를 출력

SELECT i.InviteId, i.giveUserId AS userId, u.nickname, u.profileImg, i.postId, p.title, p.postImg, 
    (SELECT COUNT(userId) FROM Channels WHERE postId = p.postId) AS currentMember,
    p.maxMember, p.startDate, p.endDate, p.place
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
JOIN Users AS u
ON i.giveUserId = u.userId
WHERE i.receiveUserId = 3
ORDER BY i.postId
LIMIT 0, 5
;


-- POST /api/room/confirm

Table 수정 
ALTER TABLE Channels ADD COLUMN confirm tinyint NOT NULL DEFAULT 0 AFTER userId; 
 - 확정 버튼을 위한 테이블 컬럼을 추가한다.



-- PUT : /api/post confirm Check 확정 상태를 확인하는 SQL

1. 확정 버튼을 누른 사람과 currentMember를 출력한다.

SELECT
    (SELECT COUNT(confirm) FROM Channels WHERE postId = 40 AND confirm = 1) AS confirmCount,
    COUNT(userId) AS currentMember
FROM Channels AS c
WHERE c.postId = 40
;


-- DELETE : /api/post confirm Check 확정 상태를 확인하는 SQL

1. 자신을 제외한 다른 사람이 있는지 확인한다.

SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId != 1;

2. CASE THEN 으로  'Y' 'N'을 출력하도록 설정한다.

SELECT 
   CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId != 1) THEN 'Y' ELSE 'N' END as isUser
;


-- POST /api/room/confirm 대화방 확정하기

1. 자신이 방장인지 확인

SELECT DISTINCT 1 FROM Posts WHERE userId = 1 AND postid = 1;
 - 방장일 경우 1 출력

SELECT
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Posts WHERE userId = 1 AND postid = 1 ) THEN 'Y' ELSE 'N' END AS isMaster
;
 - CASE WHEN 으로 'Y', 'N' 을 출력하도록 설정


2. 다른 유저들이 confirm을 눌렀는지 확인

SELECT COUNT(confirm) FROM Channels WHERE postId = 1 AND confirm = 1

SELECT 
   (SELECT COUNT(confirm) FROM Channels WHERE postId = 1 AND confirm = 1) AS confirmCount 
;


3. currentMember 출력
SELECT 
   (SELECT COUNT(userId) FROM Channels WHERE postId = 1) AS currentMember
;

4. 코드 병합
SELECT
   (SELECT COUNT(confirm) FROM Channels WHERE postId = 1 AND confirm = 1) AS confirmCount ,
   (SELECT COUNT(userId) FROM Channels WHERE postId = 1) AS currentMember,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Posts WHERE userId = 1 AND postid = 1 ) THEN 'Y' ELSE 'N' END AS isMaster
;


-- CREATE  All Posts VIEW

DROP VIEW IF EXISTS POSTS_VW ; 

CREATE VIEW POSTS_VW
AS
    SELECT p.postId, p.userId, p.title, p.postImg, 
        (SELECT COUNT(confirm) FROM Channels WHERE postId = p.postId AND confirm = 1) AS confirmCount,
        COUNT(*) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
        ST_Y(location) AS lat, ST_X(location) AS lng,
        (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem
    FROM Channels  AS c
    JOIN Posts AS p
    ON p.postId = c.postId
    GROUP BY c.postId
;

# Posts의 모든 데이터를 보는 View


 SHOW FULL TABLES IN FinalProject2 where Table_type LIKE 'VIEW';
 - VIEW 정보를 조회하는 방법



-- GET /api/post/posts VIEW를 이용한 구현

1. POSTS_VW 조회

SELECT * FROM POSTS_VW;

2. WHERE 조건 추가 

SELECT * 
FROM POSTS_VW 
WHERE confirmCount < currentMember 
    AND currentMember < maxMember
    AND startDate >= NOW()
;


3. 형식에 맞게 인자 설정

SELECT postId, title, postImg, currentMember, maxMember, startDate, endDate, place, tagItem
FROM POSTS_VW
WHERE confirmCount < currentMember
    AND currentMember < maxMember
    AND startDate >= NOW()
;


4. ORDER BY 설정
SELECT postId, title, postImg, currentMember, maxMember, startDate, endDate, place, tagItem
FROM POSTS_VW
WHERE confirmCount < currentMember
    AND currentMember < maxMember
    AND startDate >= NOW()
ORDER BY startDate
;

5. LIMIT 설정

SELECT postId, title, postImg, currentMember, maxMember, startDate, endDate, place, tagItem
FROM POSTS_VW
WHERE confirmCount < currentMember
    AND currentMember < maxMember
    AND startDate >= NOW()
ORDER BY startDate
LIMIT 0, 5
;

-- GET /api/search/post VIEW를 이용한 구현


1. GET /api/post/posts의 Query를 가져온다.

SELECT postId, title, postImg, currentMember, maxMember, startDate, endDate, place, tagItem
FROM POSTS_VW
WHERE confirmCount < currentMember
    AND currentMember < maxMember
    AND startDate >= NOW()
ORDER BY startDate
LIMIT 0, 5
;


2. WHERE 조건 및 ORDER BY 추가

SELECT postId, title, postImg, currentMember, maxMember, startDate, endDate, place, tagItem
FROM POSTS_VW
WHERE (confirmCount < currentMember
        AND currentMember < maxMember
        AND startDate >= NOW())
    AND (startDate >= '2021-08-03 15:00:00' 
        AND startDate < '2021-08-25 15:00:00')
    AND (title LIKE '%t%'
        OR tagItem LIKE '%t%')
ORDER BY startDate
LIMIT 0, 5
;




-- POST : /api/room/join VIEW 

1. POSTS_VW 에서 CASE WHEN으로 자신의 참여 여부를 조회하는 것만 추가한다.

SELECT confirmCount, currentMember, maxMember, 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS 'isExist'
FROM POSTS_VW
WHERE postId = 2
;


-- POST : /api/room/exit VIEW

1. POSTS_VW의 사용할 인자만 조회한다.

SELECT userId, title, postImg, confirmCount, currentMember, maxMember, startDate, endDate, place, lat, lng, 
FROM POSTS_VW
WHERE postId = 2
;

2. CASE WHEN으로 isExist 를 조회한다.

SELECT userId, title, postImg, confirmCount, currentMember, maxMember, startDate, endDate, place, lat, lng, 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS 'isExist'
FROM POSTS_VW
WHERE postId = 2
;



-- POST /api/room/kick


1. 확정 여부와 현재 모임의 참여중인 인원을 조회한다.

SELECT confirmCount, currentMember
FROM POSTS_VW
WHERE postId = 3 
    AND userId = 1
;


2. 현재 모임에 targetUserId가 있는지 조회한다.

SELECT * FROM Channels WHERE postId = 3 AND userId = 2


3. CASE WHEN으로 수정한다.

SELECT 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 3 AND userId = 2) THEN 'Y' ELSE 'N' END AS isTargetExist
;

4. 코드 병합

SELECT confirmCount, currentMember,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 3 AND userId = 5) THEN 'Y' ELSE 'N' END AS isTargetExist
FROM POSTS_VW
WHERE postId = 2
    AND userId = 1
;