-
카테고리
-
세부 분야
백엔드
-
해결 여부
미해결
querydsl dto mapping & subquery 질문 남깁니다.
23.01.17 00:37 작성 조회수 2.82k
0
안녕하세요 열심히 수업듣고 따라하고있는 수강생입니다.
먼저 매번 답변주셔서 감사합니다.
질문으로는 dto mapping 에서 @queryprojection 으로 직접 조회를 해야하는 상황입니다.
글 : 채팅방 1 : N
채팅방 : 채팅 1: N
일때
DTO 는
@Data
public class QChatRoomDto {
private QaType qaApart;
private String qaContent;
private String searchPlace;
private String category;
private Long chooseMemberIdx; // 채택 / 미채택 기준
private Long roomIdx;
private Long otherMemberIdx;
private String otherMemberNickname;
private String otherMemberImgDto;
private int qaCount;
private String modifiedDate;
private String recentlyMsg;
private String recentlyMsgType;
private Long notReadCount;
@QueryProjection
public QChatRoomDto(String qaContent, String searchPlace, String category, Long chooseMemberIdx, Long roomIdx, Long otherMemberIdx, String otherMemberNickname, String otherMemberImgDto, int qaCount, LocalDateTime modifiedDate, String recentlyMsg, String recentlyMsgType, Long notReadCount) {
this.qaApart = QUESTION;
this.qaContent = qaContent;
this.searchPlace = searchPlace;
this.category = category;
this.chooseMemberIdx = chooseMemberIdx;
this.roomIdx = roomIdx;
this.otherMemberIdx = otherMemberIdx;
this.otherMemberNickname = otherMemberNickname;
this.otherMemberImgDto = otherMemberImgDto;
this.qaCount = qaCount;
this.modifiedDate = modifiedDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
this.recentlyMsg = recentlyMsg;
this.recentlyMsgType = recentlyMsgType;
this.notReadCount = notReadCount;
}
}
querydsl
public Page<QChatRoomDto> myQuestionChatRoomList(Member questionMember, List<Member> blockMembers, QaStatusType qaStatusType, Pageable pageable) {
return applyPagination(pageable, contentQuery -> contentQuery
.selectDistinct(new QQChatRoomDto(
qa.content.as("qaContent"),
qa.searchPlace.as("searchPlace"),
qa.category.name.as("category"),
qa.selectMember.idx.as("chooseMemberIdx"),
chatRoom.idx.as("roomIdx"),
chatRoom.aMember.idx.as("otherMemberIdx"),
chatRoom.aMember.nickname.as("otherMemberNickname"),
chatRoom.aMember.memberImg.as("otherMemberImgDto"),
chatRoom.qMember.qaList.size().as("qaCount"),
chatRoom.modifiedDate.as("modifiedDate"),
ExpressionUtils.as(
JPAExpressions.select(chat.content)
.from(chat)
.where(chat.chatRoom.eq(chatRoom),
chat.idx.eq(
JPAExpressions.select(chat.idx.max())
.from(chat))
), "recentlyMsg")
,
ExpressionUtils.as(
JPAExpressions.select(chat.chatType)
.from(chat)
.where(chat.chatRoom.eq(chatRoom),
chat.idx.eq(
JPAExpressions.select(chat.idx.max())
.from(chat))
), "recentlyMsgType"),
ExpressionUtils.as(
JPAExpressions.select(chat.count())
.from(chat)
.where(chat.chatRoom.eq(chatRoom),
chat.member.ne(questionMember),
chat.readMsg.isFalse()), "notReadCount")
)
)
.from(chatRoom)
.join(chatRoom.qa, qa)
.join(chatRoom.aMember, member)
.where(chatRoom.qMember.eq(questionMember),
isQaSelectMember(questionMember, qaStatusType),
blockAMembersNotIn(blockMembers),
chatRoom.idx.notIn(
JPAExpressions.select(chatRoom.idx)
.from(chatRoom)
.where(chatRoom.isLeave.contains("_" + questionMember.getIdx() + "_"))),
member.phoneNum.isNotNull()
)
.orderBy(chatRoom.modifiedDate.desc()));
}
이렇게 작업을 했습니다. 매우 이상한 쿼리이겠지만 저한텐 이게 최선이었습니다 ㅠ
여기서 궁금한점은 채팅룸 리스트를 뽑아야 하는데
recentlyMsg, recentlyMsgType
채팅 리스트의 최근 데이터를 가지고 오고싶었으나
subQuery에서 limit 1 이 먹히지 않아
저런 괴랄한? 쿼리를 만들어 작동은 되게 만들었습니다.
해서 dto mapping 할때 컬렉션 리스트를 뽑는 다른 방법이 있는지 궁금합니다.
chatRoom.chatlist 를 반환하여 queryprojection에서 가공하려 했지만
에러가 뜨면서 chatList는 반환이 안되어 질문글 남깁니다.
감사합니다.
답변을 작성해보세요.
답변 1