[필기노트] MySQL 모임 초대 DB, SQL

$ npx sequelize model:generate --name Invites --attributes giveUserId:integer,receiveUserId:integer,postId:integer

CREATE TABLE Invites(
    inviteId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    giveUserId int(11) NOT NULL,
    receiveUserId int(11) NOT NULL,
    postId int(11) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (giveUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (receiveUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

 

#DB Invites 생성
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,5,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,6,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,7,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (2,1,3);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (2,3,3);

 

 

-- POST /api/room/join 현재 대화방에 참여 여부, currentMember를 출력하는 SQL


1. 데이터가 있을 경우 1을 출력하도록 설정 

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


2. 데이터가 있을 경우 'Y', 없을 경우 'N'을 출력하도록 CASE WHEN 작성

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


3. currentMember를 출력하도록 설정

SELECT COUNT(userId) AS currentMember
FROM Channels
WHERE postId = 1
GROUP BY postId
;


4. 코드 병합
SELECT COUNT(userId) AS currentMember,
CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS 'isExist'
FROM Channels
WHERE postId = 2
GROUP BY postId
;


5. maxMember 출력

SELECT maxMember FROM Posts WHERE postId = 2;


6. Posts 테이블을 JOIN하고 maxMember를 추가하도록 코드 병합

SELECT COUNT(c.userId) AS currentMember,
    p.maxMember,    
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS 'isExist'
FROM Channels AS c
JOIN Posts AS p 
ON c.postId = p.postId
WHERE c.postId = 2
GROUP BY c.postId
;
 - SubQuery를 줄일 방법을 찾아봐야 할 듯 
   - WHERE절에 AND c.userId = 1을 사용할 경우 currentMember가 1로 변경된다.
 - 개선할 수 있을 것 같은데 시간 관계상 마음의 여운을 남긴체 넘어감 ㅠ

Sequelize로 변환 ☆
 - include를 사용하였을 경우 ON 키워드를 사용할 수 없어 난관에 빠짐
 - 시간 관계상 우선 Raw Query를 사용해서 해결



-- GET /api/post/posts/location 인원이 남아있는 경우에만 출력하도록 설정

1. postId, 좌표, maxMember를 출력하도록 설정
SELECT postId, maxMember, ST_Y(location) AS lat, ST_X(location) AS lng FROM Posts;

2. currentMember 추가
SELECT p.postId, 
    COUNT(c.userId) AS currentMember,
    p.maxMember, ST_Y(p.location) AS lat, ST_X(p.location) AS lng
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE p.location IS NOT NULL 
GROUP BY c.postId
HAVING currentMember < maxMember
;


-- POST /api/room/exit lat, lng를 가져오도록 설정

1. 모든 위치 정보 출력

SELECT ST_Y(p.location) AS lat, ST_X(p.location) AS lng
FROM Posts AS p
;

2. currentMember, maxMember 출력

SELECT COUNT(c.userId) AS currentMember, p.maxMember,
    ST_Y(p.location) AS lat, ST_X(p.location) AS lng
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
GROUP BY c.postId
;


3. Channels에서 userId가 참여중인지 확인

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


4. 코드 병합
SELECT COUNT(c.userId) AS currentMember, p.maxMember,
    ST_Y(p.location) AS lat, ST_X(p.location) AS lng,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS isExist
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE p.postId = 2
GROUP BY c.postId
;


5. title, postImg, startDate, endDate, place 추가 
SELECT p.title, p.postImg, COUNT(c.userId) AS currentMember, p.maxMember,
    p.startDate, p.endDate, p.place,
    ST_Y(p.location) AS lat, ST_X(p.location) AS lng,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS isExist
FROM Posts AS p
JOIN Channels AS c
ON p.postId = c.postId
WHERE p.postId = 2
GROUP BY c.postId
;



--GET /api/post/posts/invite 자신이 방장인 모임의 목록을 출력

1. 작성한 유저가 자신일 경우 출력
SELECT * FROM Posts WHERE userId = 1;

2. currentMember를 추가한다.
SELECT p.postId, p.title, p.postImg, 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
WHERE p.userId = 1
GROUP BY c.postId
HAVING currentMember < maxMember
;

3. Limit을 추가한다.
SELECT p.postId, p.title, p.postImg, 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
WHERE p.userId = 1
GROUP BY c.postId
HAVING currentMember < maxMember
LIMIT 0, 5
;



-- post /api/room/invite 초대하기 위해 대화방에 참여중인지, 초대를 이미 보냈는지 조회한다.

1. 모든 초대 목록을 조회한다.

SELECT * FROM Invites;

2. giveUserId, receiveUserId, postId를 조회해 초대를 보낸 적이 있는지 확인한다.

SELECT DISTINCT 1 FROM Invites WHERE giveUserId =1 AND receiveUserId = 5 AND postId = 2;

3. CASE WHEN으로 병합한다.

SELECT 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Invites WHERE giveUserId =1 AND receiveUserId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isInvite
;

4. 해당하는 유저가 이미 대화방에 참여중인지 조회한다.

SELECT * FROM Channels WHERE userId = 5 AND postId = 2;


5. CASE WHEN으로 병합한다.

SELECT 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Invites WHERE giveUserId =1 AND receiveUserId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isInvite,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE userId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isExist
;

6. 조회하려는 유저가 방장인지 조회한다.

SELECT DISTINCT 1 FROM Posts WHERE postId = 2 AND userId = 1;


7. CASE WHEN으로 병합한다.

SELECT 
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Invites WHERE giveUserId =1 AND receiveUserId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isInvite,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE userId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isExist,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Posts WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS isMaster
;

8. 해당하는 방의 currentMember, maxMember를 추가한다.

SELECT COUNT(c.userId) AS currentMember, p.maxMember
FROM Posts AS p
JOIN Channels AS c
ON p.postId= c.postId
WHERE p.postId = 2 
GROUP BY c.postId
;

9. 코드 병합

SELECT COUNT(c.userId) AS currentMember, p.maxMember,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Invites WHERE giveUserId =1 AND receiveUserId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isInvite,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE userId = 5 AND postId = 2) THEN 'Y' ELSE 'N' END AS isExist,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Posts WHERE postId = 2 AND userId = 1) THEN 'Y' ELSE 'N' END AS isMaster
FROM Posts AS p
JOIN Channels AS c
ON p.postId= c.postId
WHERE p.postId = 3
GROUP BY c.postId
;



-- POST /api/room/invite/accept 초대를 수락하기 전 확인하는 API

1. 모든 Invites를 출력한다.

SELECT * FROM Invites;


2. inviteId를 이용해 초대하는 방의 정보를 가져온다.

SELECT i.postId, p.title, p.maxMember 
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
WHERE i.inviteId = 6
;


3. 해당하는 방의 currentMember를 추가로 가져온다.

SELECT i.postId, p.title, COUNT(c.userId) AS currentMember, p.maxMember 
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
JOIN Channels AS c
ON c.postId = p.postId
WHERE i.inviteId = 6
GROUP BY c.postId
;

4. 해당하는 방의 자신이 참여하고있는지 확인한다.

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


5. 코드 병합

SELECT i.postId, p.title, COUNT(c.userId) AS currentMember, p.maxMember,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE userId = i.receiveUserId AND postId = i.postId) THEN 'Y' ELSE 'N' END AS isExist
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
JOIN Channels AS c
ON c.postId = p.postId
WHERE i.inviteId = 6
GROUP BY c.postId
;
 - Invites의 userId를 기준으로 검색하는 경우


SELECT i.postId, COUNT(c.userId) AS currentMember, p.maxMember,
    CASE WHEN 1 = (SELECT DISTINCT 1 FROM Channels WHERE userId = 1 AND postId = i.postId) THEN 'Y' ELSE 'N' END AS isExist
FROM Invites AS i
JOIN Posts AS p
ON i.postId = p.postId
JOIN Channels AS c
ON c.postId = p.postId
WHERE i.inviteId = 9
    AND i.receiveUserId = 1
GROUP BY c.postId
;
 - 자신의 userId(1)를 입력해서 검색하는 경우
 - p.title을 제외함