sql subquery & select exists

테이블 관계

• Posts.hostId는 Users.id를 참조함 (fk)
• Posts.guestId는 Users.id를 참조함 (fk)
• Thumbsups.postId는 Posts.id를 참조함 (fk)
• Thumbsups.giverId는 Users.id를 참조함 (fk)
• Thumbsups.receiverId는 Users.id를 참조함 (fk)

가져올 데이터

Thumbsups 테이블을 조인시키지 않고, 서브쿼리를 사용해서 Thumbsups 테이블 필드로 새로운 테이블을 만든 후 boolean값을 가져온다.

• Posts 테이블의 hostId, guestId, location(json) 중 place_name, reserved_at, isMatched
• Users 테이블의 hosts.nickname, guests.nickname
• Thumbsups 테이블에서 특정 게시물에 특정 유저가 좋아요를 눌렀는지 여부 (boolean)

어려웠던 부분

처음에는 Thumbsups.postId가 Posts.id를 참조하므로, LEFT JOIN을 시켜보았다. 그런데, 그 결과, 한 게시물에 두 명의 유저가 서로에게 좋아요를 누른 경우, 두개의 데이터를 모두 가져오게 되었다. 1번 게시물에서 A유저가 B유저에게 좋아요를 누르고, B유저도 A유저에게 좋아요를 누른 경우, 실제로 필요한 데이터는 req.params로 전달받는 userId와 일치하는 유저가 좋아요를 눌렀는지 여부만 필요함에도 불필요하게 두개의 데이터를 모두 가져오게 되었다.

해결

서브쿼리와 SELECT EXISTS 사용

const sql = 
"SELECT EXISTS 
(SELECT giverId 
 FROM Thumbsups AS thumbsup 
 WHERE thumbsup.giverId= ? AND thumbsup.postId = post.id )) AS thumbsup
"

서브쿼리

Thumbsups 테이블에서 특정 조건을 만족하는 새로운 테이블을 생성한다.

Thumbsups.giverId가 req.params로 주어지는 userId와 일치하고, Thumbsups.postId가 이미 LEFT JOIN된 post 테이블의 id와 일치하는 경우, Thumbsups.giverId를 가져온다.

SELECT EXISTS

만약 서브쿼리에서 생성한 새로운 테이블에 데이터가 있다면 thumbsup 필드에 1을, 없다면 0을 출력한다.

아래의 예시에서는 thumbsup.postId=100이 존재하지 않으므로 0이 출력되고, thumbsup.postId=1은 존재하므로 1이 출력된다.

전체 쿼리문

 const sql = 
"
SELECT post.hostId, hosts.nickname AS hostNickname, post.guestId, guests.nickname AS guestNickname, 
post.reserved_at, JSON_UNQUOTE( JSON_EXTRACT(location, '$.place_name') ) AS placeName, post.isMatched,
(SELECT EXISTS (SELECT giverId FROM Thumbsups AS thumbsup WHERE thumbsup.giverId= ? AND thumbsup.postId = post.id )) AS thumbsup 
FROM Posts AS post 
LEFT JOIN Users AS hosts 
ON post.hostId = hosts.id 
LEFT JOIN Users AS guests 
ON post.guestId = guests.id 
WHERE guestId = ? OR hostId = ?
"