Sql_function
SQL Function
| Write | contect | Date |
|---|---|---|
| ๊นํํธ | lhyun058@gmail.com | 2022.08.13 |
๐์ ๋ฆฌ
์ขํ ๊ฐ ๊ฑฐ๋ฆฌ๋ฅผ ๊ณ์ฐํด ๊ฐ๊น์ด ๊ฑฐ๋ฆฌ ์์ผ๋ก ๋ฐ์ดํฐ๋ฅผ ๊ฐ์ ธ์ค๊ธฐ ์ํด MySQL์ ST_Distance_Sphere()ํจ์๋ฅผ ์ฌ์ฉํ ๊ฒ์ด๋ฉฐ. ํ์ฌ ์งํํ๋ ํ๋ก์ ํธ์์๋ Querydsl์ ์ฌ์ฉ ํ๊ธฐ๋๋ฌธ์ Querydsl์์ SQL Function์ฌ์ฉ์ ํตํด ์ฟผ๋ฆฌ๋ฅผ ์์ฑํด์ผ ํด์ ์ฌ์ฉ๋ฒ์ ์ ๋ฆฌํ๋ ค๊ณ ํฉ๋๋ค.
1. ST_Distance_Sphere()
ST_Distance_Sphere()๋ ๋ ์ ์ฌ์ด์ ๊ฑฐ๋ฆฌ๋ฅด meter๋จ์๋ก ๋ณํํ๋ ํจ์์
๋๋ค.
SET @location = POINT(127.036377, 37.500643);
SELECT ST_Distance_Sphere(@location,
POINT(trade_longitude, trade_latitude)) AS distance,
deal.trade_longitude,
deal.trade_latitude
FROM deal
order by distance;

2.querydsl์์ SQL Function ํธ์ถํ๊ธฐ
SQL Function์ Expressions.stringTemplate()๋ฅผ ํตํด ์ฌ์ฉํ ์ ์์ต๋๋ค.
Expressions.stringTemplate()์ ์ฌ์ฉํด ๋ฌธ์์ด๋ก ์ง์ ํจ์๋ฅผ ์์ฑ ํ๋ฉฐ, StringExpression์ ๋ฐํํ์ฌ select๋ฌธ์์ ์ฌ์ฉํ ์ ์๊ฒ ํด์ค๋๋ค.
์ฌ์ฉ๋ฐฉ๋ฒ - Expressions.stringTemplate("ST_Distance_Sphere({0}, {1})",ํ๋ผ๋ฏธํฐ1, ํ๋ผ๋ฏธํฐ2)
List<Deal> content = queryFactory
.selectFrom(deal)
.where(
categoryEq(dealFilterRequest.getCategory()),
categoryDetailEq(dealFilterRequest.getCategoryDetail()),
deal.deletedAt.isNull()
)
.join(deal.user, user).fetchJoin()
.join(deal.location, location).fetchJoin()
.limit(pageable.getPageSize())
.offset(pageable.getOffset())
.orderBy(Expressions.stringTemplate("ST_Distance_Sphere({0}, {1})",
Expressions.stringTemplate("POINT({0}, {1})",
dealFilterRequest.getCurrentLongitude(),
dealFilterRequest.getCurrentLatitude()
),
Expressions.stringTemplate("POINT({0}, {1})",
deal.tradeLongitude,
deal.tradeLatitude
)
).asc(),
deal.createdAt.desc())
.fetch();
์์ ์ฝ๋๋ฅผ ์คํํ๋ฉด ์๋์ฒ๋ผ ์ฟผ๋ฆฌ๊ฐ ๋๊ฐ๋๊ฑธ ํ์ธํ ์ ์์ต๋๋ค.
