❗️You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
const rooms = prismaClient.$queryRaw`SELECT
rooms.id,
rooms.title,
rooms.type,
rooms.province,
rooms.city,
JSON_ARRAYAGG(CASE WHEN rooms_image.id IS NOT NULL AND rooms_image.image IS NOT NULL THEN JSON_OBJECT('id', rooms_image.id, 'url',rooms_image.image) END) images,
IFNULL(rooms_like.isLike,0) islike,
MAX(room_type.max_limit) max_limit,
MIN(room_type.min_limit) min_limit,
MAX(room_type.price) max_price,
MIN(room_type.price) min_price,
IFNULL(rooms_like_sum.likes,0) likes,
theme.name theme
FROM rooms
LEFT JOIN rooms_image
ON rooms.id = rooms_image.rooms_id
LEFT JOIN (SELECT id,rooms_id, isLike FROM likes WHERE user_id=${id} group by id) as rooms_like
ON rooms_like.rooms_id = rooms.id
${generateJoinDateStatement(date.start_date, date.end_date)}
LEFT JOIN (SELECT rooms_id, SUM(isLike) likes FROM likes group by rooms_id) rooms_like_sum
ON rooms_like_sum.rooms_id = rooms.id
LEFT JOIN (SELECT rooms_id, theme.name FROM theme JOIN rooms_theme on theme.id = rooms_theme.theme_id) theme
ON theme.rooms_id = rooms.id
LEFT JOIN reservation on room_type.id = reservation.room_type_id
GROUP BY rooms.id, islike
${generateHavingStatement(filter)}
${generateOrderByStatemnet(sortKeyword)}
`);
위 코드가 계속 에러가 발생했다. 근데 prismaClient.$queryRaw를 지우고 rooms를 출력해서 나온 쿼리를 실행해보면 문제가 발생하지 않았다.
1시간 동안 구글링을 하다가 prisma 공식문서를 보고 해답을 찾았다..
바로 $queryRaw는 테이블명 또는 컬럼명이 포함되는 템플릿 리터럴을 지원하지 않았다. 그래서 계속 에러가 발생했다.
$queryRawUnsafe 메서드를 사용하면 테이블명 또는 컬럼명이 포함되는 원시 문자열(또는 템플릿 문자열)을 데이터베이스에 사용할 수 있다고 친절히 설명 되어 있었다..
const rooms = prismaClient.$queryRawUnsafe(`SELECT
rooms.id,
rooms.title,
rooms.type,
rooms.province,
rooms.city,
JSON_ARRAYAGG(CASE WHEN rooms_image.id IS NOT NULL AND rooms_image.image IS NOT NULL THEN JSON_OBJECT('id', rooms_image.id, 'url',rooms_image.image) END) images,
IFNULL(rooms_like.isLike,0) islike,
MAX(room_type.max_limit) max_limit,
MIN(room_type.min_limit) min_limit,
MAX(room_type.price) max_price,
MIN(room_type.price) min_price,
IFNULL(rooms_like_sum.likes,0) likes,
theme.name theme
FROM rooms
LEFT JOIN rooms_image
ON rooms.id = rooms_image.rooms_id
LEFT JOIN (SELECT id,rooms_id, isLike FROM likes WHERE user_id=${id} group by id) as rooms_like
ON rooms_like.rooms_id = rooms.id
${generateJoinDateStatement(date.start_date, date.end_date)}
LEFT JOIN (SELECT rooms_id, SUM(isLike) likes FROM likes group by rooms_id) rooms_like_sum
ON rooms_like_sum.rooms_id = rooms.id
LEFT JOIN (SELECT rooms_id, theme.name FROM theme JOIN rooms_theme on theme.id = rooms_theme.theme_id) theme
ON theme.rooms_id = rooms.id
LEFT JOIN reservation on room_type.id = reservation.room_type_id
GROUP BY rooms.id, islike
${generateHavingStatement(filter)}
${generateOrderByStatemnet(sortKeyword)}
`);
위 코드로 작성하니 에러가 발생하지 않고 정상적으로 동작하는 것을 확인할 수 있었다.
오.. 역시 공식문서가 👍이네요 ㅋㅋ