지금 생각해보니 당근 측에서 보내주는 메시지는 구현하지 않았다만 중요한 기능은 아니므로 제외했다.
위 기능들을 고려하여 아래와 같이 제작했다.
select ChatRoom.crId, User.imgUrl as RoomImgUrl, userNick as roomName, forLastMessage.chatsId, Goods.goodsId, PG.imgUrl,
case
when TIMESTAMPDIFF(YEAR, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(YEAR, forLastMessage.lastMessageTime, current_timestamp), '년 전')
when TIMESTAMPDIFF(MONTH, forLastMessage.lastMessageTime, current_timestamp) > 3 then
CONCAT(TIMESTAMPDIFF(MONTH, forLastMessage.lastMessageTime, current_timestamp), '달 전')
when TIMESTAMPDIFF(WEEK, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(WEEK, forLastMessage.lastMessageTime, current_timestamp), '주 전')
when TIMESTAMPDIFF(DAY, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(DAY, forLastMessage.lastMessageTime, current_timestamp), '일 전')
else DATE_FORMAT(forLastMessage.lastMessageTime, '%p %H : %i') end as lastMessageTime,
PostRegion.regionName,
case when forLastMessage.lastMessageType = 't' then forLastMessage.lastContent
when forLastMessage.lastMessageType = 'p' then concat('이미지 링크:', forLastMessage.lastContent)
else '타입 미설정' END as 'ChatContent'
from ChatRoom
inner join User on ChatRoom.sellerId = User.userId
inner join Goods on ChatRoom.sellerId = Goods.sellerId
inner join (select goodsId, imgUrl from PictureGoods where status = 'n' order by PictureGoods.pgId) PG on Goods.goodsId = PG.goodsID
inner join (select Goods.rlId, regionName from Goods inner join
(select rlId, regionName from RegionList) as Region
where Region.rlId = Goods.rlId) as PostRegion on Goods.rlId = PostRegion.rlId
inner join (select Chats.crId, Chats.chatsId, Chats.content as lastContent, Chats.createdAt as lastMessageTime
,Chats.type as lastMessageType from Chats
inner join (select Chats.crId, max(chatsId) as maxChatsId from Chats group by crId) lastMessage
where maxChatsId = Chats.chatsId) as forLastMessage on ChatRoom.crId = forLastMessage.crId
group by forLastMessage.crId;
1.ChatRoom (채팅방) 테이블
일단 현재 총 3개를 예시로 구현했다. 각각 유저 1-2번, 3-4번, 2-1번이 매칭되어 있다. 참고로 1,2,3,4 모두 User NickName은 각각 A,B,C,D이다.
User를 모두 보관하고 있는 테이블
위에서 설명한 각 4명을 임의로 테이블을 구성했다. imgUrl은 각각의 프로필 사진 링크이다. 뒤에 데이터가 더 있으나 위에서 설명할 것은 없으므로 생략했다.
상품판매 게시판 테이블이다.
당근마켓의 경우 해당 게시판의 판매자를 클릭해 채팅을 하도록 되어 있으므로 Join이 필요하다. 위 사진에서는 sellerId를 핵심적으로 보면 된다.
sellerId는 참고로 각각 위에서 설명했듯 2,4,1로 B,D,A이다.
상품 판매 게시판에 연동되어있는 다양한 사진들을 보관하는 테이블이다.
해당 채팅방 메인 화면에서 상품 사진을 확인할 수 있어야 하므로 연동했다. 참고 사항으로 1번 게시물에서 첫 사진은 삭제된 상태이다. (status가 삭제 여부를 의미 삭제 시 y / 삭제된 게 아닐 시 n)
채팅 테이블
말 그대로 채팅 테이블이다. 색칠한 대로 노란색이 1번 방, 초록색이 2번 방, 파란 색이 3번 방 내의 채팅이다. 시간 순으로 정렬되어 있다. fromId는 해당 채팅을 보낸 사람이고 toId는 해당 채팅을 받는 사람이다.
임의로 구현한 동네들 목록
위 테이블들을 참고해서 아래 내용을 해석해보자. 우선 결과값은 아래와 같이 나온다.
우측부터 순서대로 해당 채팅방 ID, 해당 채팅방 프로필 사진, 해당 채팅방 이름, 해당 채팅방 마지막 채팅 ID, 해당 채팅방 대표 상품 썸네일, 마지막 메시지의 전송 시간이 현재로부터 얼마나 지났는지, 상품 거래 희망 지역, 마지막 채팅 내용이다.
case
when TIMESTAMPDIFF(YEAR, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(YEAR, forLastMessage.lastMessageTime, current_timestamp), '년 전')
when TIMESTAMPDIFF(MONTH, forLastMessage.lastMessageTime, current_timestamp) > 3 then
CONCAT(TIMESTAMPDIFF(MONTH, forLastMessage.lastMessageTime, current_timestamp), '달 전')
when TIMESTAMPDIFF(WEEK, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(WEEK, forLastMessage.lastMessageTime, current_timestamp), '주 전')
when TIMESTAMPDIFF(DAY, forLastMessage.lastMessageTime, current_timestamp) >= 1 then
CONCAT(TIMESTAMPDIFF(DAY, forLastMessage.lastMessageTime, current_timestamp), '일 전')
else DATE_FORMAT(forLastMessage.lastMessageTime, '%p %H : %i') end as lastMessageTime
case when ~ then ~ ... end 문법을 이용해 구현했다.
현재시각(current_timestamp)와 메시지의 lastMessageTime을 비교해서 구현했다.
inner join
(select Chats.crId, Chats.chatsId, Chats.content as lastContent, Chats.createdAt as lastMessageTime
,Chats.type as lastMessageType from Chats
inner join (select Chats.crId, max(chatsId) as maxChatsId from Chats group by crId) lastMessage
where maxChatsId = Chats.chatsId) as forLastMessage on ChatRoom.crId = forLastMessage.crId
from 내부에 inner join 연속되어 사용되는데 그 중 하나이다.
결국 핵심은 max(chatsId)이다. 가장 마지막 채팅은 해당 채팅들 중 chatsId가 가장 클 수 밖에 없는데 (auto increase) 이를 응용해 마지막 채팅을 찾아낼 수 있다. 이 메시지를 last message로 구하고 inner join한 것이다.
참고로 생성된 날짜로도 할 수는 있다. (timestamp 비교)
select User.imgUrl as RoomImgUrl...
...
from ChatRoom...
inner join User on ChatRoom.sellerId = User.userId
확인할 수 있다 싶이, 채팅방의 판매자의 사진을 불러오고 싶은 것이므로 User와 inner join하여 User의 프로필 사진 링크를 불러왔다.
방 이름(RoomName)도 같은 원리로 판매자의 NickName으로 설정했다.
Select PostRegion.regionName...
...
inner join
(select rlId, regionName from RegionList) as Region
where Region.rlId = Goods.rlId) as PostRegion on Goods.rlId = PostRegion.rlId
임의로 동네 리스트를 구현하여 중고 상품 게시판(Goods)내에 저장했었다. 이를 활용하여 Goods의 동네지역 Id 값과 같은 동네 리스트(RegionList)의 Id 값을 불러와 채팅방 메인에 표시하였다.
예시의 사진을 보면 우측에 상품이 있을 경우 상품 사진도 첨부된다. 이 때 해당 사진이 삭제된 사진일 경우 표시되면 안된다.
select PG.imgUrl...
from ...
inner join (select goodsId, imgUrl from PictureGoods where status = 'n' order by PictureGoods.pgId) PG
on Goods.goodsId = PG.goodsID
게시물에 올려놓은 사진들 중에 status가 n인 것들 (삭제되지 않은 것)만 불러와 생성된 순서대로(Key값 응용) 정렬하였다. 위에서 설명했던 원인과 동일하다.
select DATE_FORMAT(C.createdAt, '%p %H : %i') as MessageTime, userNick,fromId, C.status, imgUrl, chatsId,
case when C.type = 't' then C.content
when C.type = 'p' then concat('이미지 링크:', C.content)
when C.type = 'e' then concat(C.content, '\n',userNick, ' 님이 이모티콘을 보냈습니다.')
else '지원되지 않는 형식입니다.' END as 'ChatContent'
,type, isChecked
from ChatRoom
inner join Chats C on ChatRoom.crId = C.crId
inner join User on C.fromId = User.userId
where ChatRoom.goodsId = 3
order by MessageTime asc;
해당 채팅 내용이 언제 작성되었는지와 그 메시지 타입에 따라 다른 타입으로 보여줌.(현재 사진이나 이모티콘 로드하는 법을 몰라 일단 텍스트로 대체함)
Chat 테이블과 User 테이블과 ChatRoom 테이블을 조인하여 일단 텍스트 메시지를 확인할 수 있게끔 구현함.
select userNick,
case when TransactionReview.trId <> null then 36.5 + sum(if(TransactionReview.isGood = 'g', 0.1, -0.1))
else 36.5 end as UserTemp,
case when G.status = 'y' then '거래완료'
when G.status = 's' then '판매 중'
when G.status = 'r' then '예약 중'
else '삭제됨' end as Status, G.title, G.price,
case when G.canBargain = 'y' then '가격제안가능'
else '가격제안불가' end as canBargain,
case when TransactionReview.userId = 1
//후기를 남긴 유저가 있고 그게 본인 ID일시 (임의로 일단 현재 접속한 유저 ID를 buyerId라고 가정함)
then '보낸 후기 보기'
else '후기를 남겨주세요.' end as review
from ChatRoom CR
inner join User U on CR.sellerId = U.userId
inner join Goods G on U.userId = G.sellerId
left join TransactionHistory on U.userId = TransactionHistory.sellerId
left join TransactionReview on TransactionHistory.thId = TransactionReview.thId
where CR.crId = 1; // 현재 들어간 채팅방
재거래희망률의 경우 거래 이후 TransactionHistory 혹은 Review 테이블을 이용해 좋았던 것과 싫었던 것을 다 계산해서 전체 거래횟수랑 비교하면 얼추 구할 수 있을 것 같은데
응답률이 문제였다. 응답률의 경우 채팅을 통해 몇 분 내에 보통 대답을 하는지를 어떻게 구현할지가 관건이었는데
처음 구매자의 질문에 판매자가 답변하는 시간만 측정할지 아니면 이후 구매자가 보낸 질문에 대해서도 판매자가 답하는데 걸리는 시간 또한 측정을 해야할지 매우 고민이었다.
-> 채팅방 생성 후 첫 대답에 대해서 대답하는 경우만 응답률로 계산하기로 하였다.
# 응답률 계산
select IFNULL(FORMAT(count(distinct forFirstMessage.chatsId) / count(distinct ifnull(ChatRoom.crId, 0)) * 100, 0), '-') as responseRate
from ChatRoom
inner join (select Chats.crId, Chats.chatsId, Chats.content as firstContent, Chats.createdAt as firstMessageTime
,Chats.type as firstMessageType from Chats
inner join (select Chats.fromId,Chats.crId, min(chatsId) as minChatsId from Chats where fromId = 4 #여기 수정
group by crId) firstMessage
where minChatsId = Chats.chatsId) as forFirstMessage on ChatRoom.crId = forFirstMessage.crId and ChatRoom.sellerId = 4; # 여기 수정
# 응답하는데 걸리는 시간 계산
select
case when AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)) > 60 then '몇 시간 이내 응답'
when AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)) > 0 then
concat(AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)), '분 이내 응답')
else '표시될만큼 충분히 대화하지 않았어요' end as responseTime
from ChatRoom
inner join (select Chats.crId, Chats.chatsId, Chats.content as firstContent, Chats.createdAt as firstMessageTime
,Chats.type as firstMessageType from Chats
inner join (select Chats.fromId,Chats.crId, min(chatsId) as minChatsId from Chats where fromId = 3 #여기 수정
group by crId) firstMessage
where minChatsId = Chats.chatsId) as forFirstMessage on ChatRoom.crId = forFirstMessage.crId and ChatRoom.sellerId = 3; # 여기 수정
select G.goodsId, G.title, RL.regionName, G.sellerId,
if (G.createdAt <> G.updatedAt,'끌올', '') as newupdated,
case when timestampdiff(second , G.updatedAt, current_timestamp) <60
then concat(timestampdiff(second, G.updatedAt, current_timestamp),' 초 전')
when timestampdiff(minute , G.updatedAt, current_timestamp) <60
then concat(timestampdiff(minute, G.updatedAt, current_timestamp),' 분 전')
when timestampdiff(hour , G.updatedAt, current_timestamp) <24
then concat(timestampdiff(hour, G.updatedAt, current_timestamp),' 시간 전')
else concat(datediff( current_timestamp, G.updatedAt),' 일 전')
end as uploadTime, G.price, ifnull(chatCount, 0) as chatCount,ifnull(likeCount, 0) as likeCount, UR.rlId
from (select * from Goods where hideStatus = 'n') as G
inner join User U on U.userId = 4 #userId 접속한 본인 Id 선택
and G.sellerId <> U.userId
inner join UserRegion UR on G.rlId = UR.rlId
inner join (select rlId, regionName from RegionList) RL on UR.rlId = RL.rlId
left join (
select goodsId, imgUrl
from PictureGoods PG
where PG.status = 'n'
group by goodsId
having min(PG.pgId)
) Ph on G.goodsId = Ph.goodsId
left join (select goodsId,
count(ChatRoom.crId) as chatCount
from ChatRoom
group by goodsId
) CR on CR.goodsId = G.goodsId
left join (
select goodsId, count(userId) as likeCount
from WishList
where status = 'l'
group by goodsId
) LP on LP.goodsId = G.goodsId
order by G.updatedAt desc;
select (case when P.subjectList = '1' then '동네질문'
when P.subjectList = '2' then '동네사건사고'
when P.subjectList = '3' then '동네맛집'
when P.subjectList = '4' then '동네소식'
when P.subjectList = '5' then '취미생활'
when P.subjectList = '6' then '분실/실종센터'
when P.subjectList = '7' then '해주세요'
when P.subjectList = '8' then '일상'
when P.subjectList = '9' then '동네사진전'
else '실시간 동네날씨' end)as category,
P.content as content,
PP.postImgUrl,
U.userNick as writerNickName,
RegionList.regionName as writerLocation,
case when timestampdiff(second , P.updatedAt, current_timestamp) <60
then concat(timestampdiff(second, P.updatedAt, current_timestamp),' 초 전')
when timestampdiff(minute , P.updatedAt, current_timestamp) <60
then concat(timestampdiff(minute, P.updatedAt, current_timestamp),' 분 전')
when timestampdiff(hour , P.updatedAt, current_timestamp) <24
then concat(timestampdiff(hour, P.updatedAt, current_timestamp),' 시간 전')
else concat(datediff(current_timestamp, P.updatedAt),' 일 전')
end as uploadTime, if(ifnull(commentCount, 0) = 0,'댓글쓰기',(concat('댓글 ', commentCount))) as Comment
,concat(
(case when coalesce(emotionList, '') = '1' then 'good '
when coalesce(emotionList, '') = '2' then 'love '
when coalesce(emotionList, '') = '3' then 'fun '
when coalesce(emotionList, '') = '4' then 'astonish '
when coalesce(emotionList, '') = '5' then 'sad '
when coalesce(emotionList, '') = '6' then 'angry '
else '' end)
,if(ifnull(PostEmotionCount, 0) = 0, '', PostEmotionCount)) as Emoticon
from Post P
join User U on P.userId = U.userId
join (select UR.userId, UR.rlId
from UserRegion UR
where UR.representStatus = 'y') representUR on representUR.userId = U.userId
join RegionList on representUR.rlId = RegionList.rlId
left join (
select postId, count(commentId) as commentCount
from Comment
group by postId
) C on P.postId = C.postID
left join (
select postId, emotionList,count(PostEmotion.peId) as PostEmotionCount
from PostEmotion
group by postId
) PE on P.postId = PE.postID
left join (
select postId, min(PostImg.piId) as min, postImgUrl
from PostImg
where status ='n'
GROUP BY postId
) PP on P.postId = PP.postId
ORDER BY P.updatedAt DESC;
INSERT INTO Keyword (content, userId) VALUES ('ex', 1);
// 현재 접속한 userId = 1로 임의 지정
UPDATE Keyword set status = 'd' where content = 'ex2' and userId = 1;
// 대표 주소일 경우 (좌측)
UPDATE UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
set alarmStatus = 'n' where UU.userId = 1 and UR.representStatus = 'y';
// 일반 주소일 경우 (우측)
UPDATE UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
set alarmStatus = 'n' where UU.userId = 1 and UR.representStatus = 'n';
select R.regionName from UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
inner join (select RL.rlId,RL.regionName from RegionList RL) R on R.rlId = UR.rlId
where UU.userId = 1 and representStatus = 'y';
select R.regionName from UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
inner join (select RL.rlId,RL.regionName from RegionList RL) R on R.rlId = UR.rlId
where UU.userId = 1 and representStatus = 'n';
# 대표 동네
UPDATE UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
set UR.rangeStatus = 'S' where representStatus = 'y' and UU.userID = 2; # 1, S, M, L 중 하나 선택 가능
# 일반 동네
UPDATE UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
set UR.rangeStatus = 'L' where representStatus = 'n' and UU.userID = 1; # 1, S, M, L 중 하나 선택 가능
UPDATE UserRegion UR
inner join (select U.userId from User U) UU on UR.userId = UU.userId
set UR.authStatus = 'n' where representStatus = 'n' and UU.userID = 1;
# 1authstatus로 인증 설정 가능, 참고로 인증한지 30일 지나면 재인증 필요
select User.userNick,User.userId,
36.5 + sum(distinct if(ifnull(TransactionReview.isGood, '0') = '0', '0', if(TransactionReview.isGood = 'g', 0.1, -0.1))) as userTemp,
if(count(distinct TransactionReview.thId) > 5,'-', (concat(ROUND(sum(distinct if(TransactionReview.isGood = 'g', 1, 0)) / count(distinct if(TransactionReview.isGood = 'g', 1, 0)) * 100, 0), '%'))) as retradeHope,
if(count(distinct TransactionReview.thId) > 5,'표시될 만큼 충분히 대화하지 않았어요', concat(count(distinct(TransactionReview.thId)), '명 중 ' ,sum(distinct if(TransactionReview.isGood = 'g', 1, 0)),'명이 만족')) as Satisfaction
,count(distinct UB.blId) as BadgeCount, concat(regionName, ' ' , authTimes, '회 인증') as UserRegionTimes, concat('최근 ', ROUND(TIME_TO_SEC(TIMEDIFF(current_timestamp,Log.createdAt))/(24 * 60 * 60), 0),'일 이내 활동') as WhenUserActive,
DATE_FORMAT(User.createdAt,'%Y년 %m월 %d일 가입') as UserRegistered,
IFNULL(FORMAT(count(distinct forFirstMessage.chatsId) / count(distinct ifnull(ChatRoom.crId, 0)) * 100, 0), '-') as responseRate,
case when AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)) > 60 then '몇 시간 이내 응답'
when AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)) > 0 then
concat(FORMAT(AVG(TIMESTAMPDIFF(MINUTE,ChatRoom.createdAt, firstMessageTime)),0), '분 이내 응답')
else '표시될만큼 충분히 대화하지 않았어요' end as responseTime
from User
inner join (select distinct userId, urid, rlid, authstatus, representstatus from UserRegion where representStatus = 'y') UR on User.userId = UR.userId
inner join (select rlId,RegionList.status, regionName from RegionList where status = 'y') RL on RL.rlId = UR.rlId and UR.userId
inner join Log on User.userId = Log.userId
left join (select * from ChatRoom) CR on CR.sellerId = User.userId
left join (select blID, userID from UserBadges) UB on User.userId = UB.userId
left join TransactionHistory on TransactionHistory.sellerId = UR.userId
left join TransactionReview on TransactionHistory.thId = TransactionReview.thId
left join ChatRoom on User.userId = ChatRoom.sellerId
left join (select Chats.crId, Chats.chatsId, Chats.content as firstContent, Chats.createdAt as firstMessageTime
,Chats.type as firstMessageType from Chats
inner join (select Chats.fromId,Chats.crId, min(chatsId) as minChatsId from Chats where fromID = 4 #여기 userId와 동일하게
group by crId) firstMessage
where minChatsId = Chats.chatsId) as forFirstMessage on ChatRoom.crId = forFirstMessage.crId and ChatRoom.sellerId = User.userId
where User.userId = 4; # userId 설정
SQL 쿼리 문법이 익숙치 않아 힘들었다. 특그리고 서브쿼리 활용(스칼라, 인라인 뷰, 내부 서브쿼리)과 in과 exist의 차이, left/right outer join 활용(아직 어떨 때 써야할 지 모르겠다.) 등등 아직 모르는 게 많아 알아볼 필요가 있다.