QueryDSL
문제
- 새로운 API 추가
- 검색 조건
- 제목 부분적 일치 검색
- 일정의 생성일 범위로 검색하고 최신순으로 정렬
- 담당자의 닉네임 부분적 일치 검색
- 검색 결과
- 제목
- 해당 일정의 담당자 수
- 해당 일정의 총 댓글 개수
- 페이징 처리
해결
- 해결 과정 - 1
문제 코드
List<TodoPageResponse> list = queryFactory
.select(Projections.constructor(
TodoPageResponse.class),
todo.title,
todo.managers.size(),
todo.comments.size())
.from(todo)
.leftJoin(todo.managers)
.leftJoin(todo.comments)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.groupBy(todo.id)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
//밑에 이런 식으로 console 에러 발생
Could not interpret path expression 'manager'count()를 사용하지 않고size()를 사용하면 위험count()와countDistinct()를 사용 해야 함
수정된 코드
List<TodoPageResponse> list = queryFactory
.select(Projections.constructor(
TodoPageResponse.class,
todo.title,
manager.countDistinct(),
comment.countDistinct())
)
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.groupBy(todo.id)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();- 해결 과정 - 2 문제 코드
Long listCount = queryFactory
.select(todo.count())
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.groupBy(todo.id)
.fetch(); Query did not return a unique result: 6 results were returned- 위의 Query문 그대로 사용했더니 결과가 6개나 있다는 오류가 뜸
- 페이징을 위해 개수를 구하는 것이므로 groupby를 제외함
- fetchOne()으로 변경해야됨
해결된 코드
Long listCount = queryFactory
.select(todo.count())
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.fetchOne();- 완성된 코드 -
QueryDSL
public Page<TodoPageResponse> findAllWithCond(TodoPageRequest request) {
Pageable pageable = PageRequest.of(request.getPage(), request.getSize());
LocalDateTime start = null;
LocalDateTime end = null;
if (request.getStartDate() != null) start = request.getStartDate().atStartOfDay();
if (request.getEndDate() != null) end = request.getEndDate().plusDays(1).atStartOfDay();
List<TodoPageResponse> list = queryFactory
.select(Projections.constructor(
TodoPageResponse.class,
todo.title,
manager.countDistinct(),
comment.countDistinct())
)
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.groupBy(todo.id)
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
Long listCount = queryFactory
.select(todo.count())
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.fetchOne();
long total = listCount != null ? listCount : 0L;
return new PageImpl<>(list, pageable, total);
}
//제목 부분 일치 확인
private BooleanExpression titleCont(String title) {
return title == null ? null : todo.title.contains(title);
}
//생성일 범위 확인
private BooleanExpression dateEq(LocalDateTime startDate, LocalDateTime endDate) {
if(startDate != null && endDate != null) return todo.createdAt.between(startDate, endDate);
else if (startDate != null) return todo.createdAt.goe(startDate);
else if (endDate != null) return todo.createdAt.loe(endDate);
else return null;
}
//담당자 닉네임 부분 확인
private BooleanExpression nicknameCont(String nickname) {
return nickname == null ? null : manager.user.nickname.contains(nickname);
}- 완성된 코드 -
Controller
@GetMapping("/todos/d")
public ResponseEntity<Page<TodoPageResponse>> getTodoPage(TodoPageRequest request) {
return ResponseEntity.ok(todoService.getTodoPage(request));
}- 완성된 코드 -
Service
@Transactional(readOnly = true)
public Page<TodoPageResponse> getTodoPage(TodoPageRequest request) {
return todoRepository.findAllWithCond(request);
}- 완성된 코드 -
Request
@Getter
public class TodoPageRequest {
private final String title;
private final LocalDate startDate;
private final LocalDate endDate;
private final String nickname;
private final Integer page;
private final Integer size;
public TodoPageRequest(String title,
LocalDate startDate,
LocalDate endDate,
String nickname,
Integer page,
Integer size) {
this.title = title;
this.startDate = startDate;
this.endDate = endDate;
this.nickname = nickname;
this.page = page == null ? 0 : page;
this.size = size == null ? 10 : size;
}
}page와size를defaultValue를 설정- 새로운
API를 만들어서 일정을 검색하는 기능을 구현 Projections를 활용해서 필요한 필드만 페이징 반환
더 고민한 해결
- 생성일 최신순으로 정렬을 안한 문제
Could not interpret path expression 'manager.user.nickname’에러 발생join users u1_0 on u1_0.id = m1_0.user_id으로 쿼리를 날림
# nickname 만 검색했을 때
select t1_0.title,
count(distinct m1_0.id),
count(distinct c1_0.id)
from todos t1_0
left join managers m1_0 on t1_0.id = m1_0.todo_id
join users u1_0 on u1_0.id = m1_0.user_id
left join comments c1_0 on t1_0.id = c1_0.todo_id
where u1_0.nickname like ? escape '!'
group by t1_0.id
order by t1_0.created_at desc
limit ?, ?
# title과 nickname을 검색했을 때
select t1_0.title,
count(distinct m1_0.id),
count(distinct c1_0.id)
from todos t1_0
left join managers m1_0 on t1_0.id=m1_0.todo_id
join users u1_0 on u1_0.id=m1_0.user_id
left join comments c1_0 on t1_0.id=c1_0.todo_id
where t1_0.title like ? escape '!'
and u1_0.nickname like ? escape '!'
group by t1_0.id order by t1_0.created_at desc limit ?,?- 해결된 코드
List<TodoPageResponse> list = queryFactory
.select(Projections.constructor(
TodoPageResponse.class,
todo.title,
manager.countDistinct(),
comment.countDistinct())
)
.from(todo)
.leftJoin(todo.managers, manager)
.leftJoin(manager.user, user)
.leftJoin(todo.comments, comment)
.where(
titleCont(request.getTitle()),
dateEq(start, end),
nicknameCont(request.getNickname())
)
.groupBy(todo.id)
.orderBy(todo.createdAt.desc())
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
//담당자 닉네임 부분 확인
private BooleanExpression nicknameCont(String nickname) {
return nickname == null ? null : user.nickname.contains(nickname);
}
Allons-y