๐Ÿ”ฅ TIL - Day 65 Querydsl Group by

Kim Dae Hyunยท2021๋…„ 11์›” 25์ผ
0

TIL

๋ชฉ๋ก ๋ณด๊ธฐ
76/93

Querydsl์˜ Group by์— ๋Œ€ํ•ด ์ฐพ์•„๋ณด๋‹ค๊ฐ€ ์šฐ์—ฐ์น˜ ์•Š๊ฒŒ ๊ธฐ์–ตํ•ด๋‘๋ฉด ์ข‹์„ ์ด์Šˆ๋ฅผ ์ฐพ์•˜๋‹ค. ๊ฐ„๋‹จํ•˜๊ฒŒ ์ •๋ฆฌํ•ด๋ณด์ž.

์ด๋ฒˆ ํ”„๋กœ์ ํŠธ์—์„œ ์–ธ์–ด๋ณ„๋กœ ๊ฐœ์ˆ˜๋ฅผ ํŒŒ์•…ํ•ด์•ผ ํ•œ๋‹ค๋Š” ์š”๊ตฌ์‚ฌํ•ญ์ด ์žˆ์—ˆ๋‹ค.
์ฒ˜์Œ ์ง  ์ฟผ๋ฆฌ๋Š” ์•„๋ž˜์™€ ๊ฐ™๋‹ค.

    @Override
    public List<ReviewRequestLanguageCount> getReviewRequestLanguageCountGroupByLanguage() {
        List<Tuple> result = query.select(reviewRequest.languageName, reviewRequest.id.count())
                .from(reviewRequest)
                .groupBy(reviewRequest.languageName)
                .fetch();

        return result.stream().map(
                r -> new ReviewRequestLanguageCount(r.get(0, String.class), r.get(1, Long.class))
        ).collect(Collectors.toList());
    }

Group by์˜ ๋Œ€์ƒ์„ ์–ธ์–ด์ด๋ฆ„(languageName)์œผ๋กœ ํ•ด์คŒ์œผ๋กœ ์นด์šดํŒ…์„ ํ•  ์ˆ˜ ์žˆ๊ฒŒ ๋˜์—ˆ๋‹ค. ๋ฐœ์ƒํ•˜๋Š” ์ฟผ๋ฆฌ๋„ ์˜ˆ์ƒํ•˜๋Š” ๋Œ€๋กœ ๋‚˜๊ฐ”๋‹ค.

์ด๋ ‡๊ฒŒ ๊ตฌํ˜„์„ ๋งˆ์น˜๊ณ  ํ˜น์‹œ๋‚˜ ์žˆ์„ ์ด์Šˆ๋ฅผ ์ฐพ์•„๋ณด๊ธฐ ์œ„ํ•ด ๊ตฌ๊ธ€๋ง ํ–ˆ๋Š”๋ฐ ์•„๋‹ˆ๋‚˜ ๋‹ค๋ฅผ๊นŒ ๊ณ ์ˆ˜๋‹˜๋“ค์˜ ์ด์Šˆ๊ฐ€ ํŠ€์–ด๋‚˜์™”๋‹ค.


๐Ÿ“Œ (MySQL) Group by์—์„œ ์ž๋™์ •๋ ฌ? ์ด์Šˆ

์ฟผ๋ฆฌ์—๋Š” ์•„๋ฌด๋Ÿฐ ๋ฌธ์ œ๊ฐ€ ์—†์ง€๋งŒ MySql์—์„œ Group by๋ฅผ ์ด์šฉํ•œ๋‹ค๋ฉด ์ž๋™์œผ๋กœ ์ •๋ ฌ์ด ์ˆ˜ํ–‰๋œ๋‹ค๊ณ  ํ•œ๋‹ค. ํ•˜์ง€๋งŒ ๊ทธ๋ฃนํ•‘์— ์ •๋ ฌ์€ ํ•„์š”์—†๋‹ค. ์ด ์“ธ๋ชจ์—†๋Š” ์ž‘์—…์„ ์ œ๊ฑฐํ•ด์•ผ ํ•œ๋‹ค.

๋ณดํ†ต์˜ MySql์—์„œ๋Š” ์œ„์™€๊ฐ™์€ ์ด์Šˆ๋ฅผ order by null๋กœ ํ•ด๊ฒฐํ•œ๋‹ค๊ณ  ํ•œ๋‹ค.
(๋ช…์‹œ์ ์œผ๋กœ ์ •๋ ฌ์„ ํ•˜์ง€ ์•Š๊ฒ ๋‹ค๋Š” ํ‘œ์‹œ ??)

Querydsl์—์„œ ์ง์ ‘์ ์œผ๋กœ order by null์„ ํ—ˆ์šฉํ•˜์ง€๋Š” ์•Š์ง€๋งŒ OrderSpecifier๋ฅผ ์ด์šฉํ•ด์„œ ์ •๋ ฌ๋™์ž‘์„ ์ปค์Šคํ…€ํ•ด์„œ ๋น„์Šทํ•˜๊ฒŒ ์ง์ ‘ ๊ตฌํ˜„ํ•  ์ˆ˜ ์žˆ๋‹ค.

jojoldu ๋‹˜์˜ ์ฝ”๋“œ๋ฅผ ๊ทธ๋Œ€๋กœ ์‚ฌ์šฉํ–ˆ๋‹ค.

public class OrderByNull extends OrderSpecifier {
    public static final OrderByNull DEFAULT = new OrderByNull();

    private OrderByNull() {
        super(Order.ASC, NullExpression.DEFAULT, NullHandling.Default);
    }
}

์ด๋ ‡๊ฒŒ Null(NullExpression.DEFAULT)์„ ์ •๋ ฌํ•˜๋Š” ์ •๋ ฌ์„ Group by๋ฅผ ์ˆ˜ํ–‰ํ•˜๋Š” ๊ณณ์— ์ ์šฉ์‹œํ‚ค๋ฉด ๋œ๋‹ค.

    @Override
    public List<ReviewRequestLanguageCount> getReviewRequestLanguageCountGroupByLanguage() {
        List<Tuple> result = query.select(reviewRequest.languageName, reviewRequest.id.count())
                .from(reviewRequest)
                .groupBy(reviewRequest.languageName)
                // ์ ์šฉ
                .orderBy(OrderByNull.DEFAULT)
                .fetch();

        return result.stream().map(
                r -> new ReviewRequestLanguageCount(r.get(0, String.class), r.get(1, Long.class))
        ).collect(Collectors.toList());
    }

์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋ฅผ ๋ณด๋ฉด order by null asc๊ฐ€ ๋‚˜๊ฐ„ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.




๐Ÿ“Œ ์ฐธ๊ณ 

Querydsl Group by ์ตœ์ ํ™”
https://jojoldu.tistory.com/477

profile
์ข€ ๋” ์ฒœ์ฒœํžˆ ๊นŒ๋จน๊ธฐ ์œ„ํ•ด ๊ธฐ๋กํ•ฉ๋‹ˆ๋‹ค. ๐Ÿง

0๊ฐœ์˜ ๋Œ“๊ธ€