QueryDSL 1:N처리[수정예정]

공부 하다가 만들거여서 테이블에 관한 설명과 QueryDSL에서 적용이 안될수도 있습니다.
이런 방식이라고 어느정도 파악용도로 작성합니다.
코드 테스트후 수정 예정입니다.

-- 임의의 테이블 생성
-- 사용자 계정
CREATE TABLE UserAccount (
    account_id SERIAL PRIMARY KEY,
    account_code VARCHAR(50),
    account_type VARCHAR(50),
    account_status VARCHAR(50)
);
-- 사용자 정보
CREATE TABLE UserProfile (
    profile_id SERIAL PRIMARY KEY,
    profile_code VARCHAR(50),
    user_name VARCHAR(100)
);
-- 사용자 활동 로그
CREATE TABLE UserActivity (
    activity_id SERIAL PRIMARY KEY,
    user_code VARCHAR(50),
    action_code VARCHAR(50),
    action_date DATE,
    action_type VARCHAR(50),
    interaction_count INT
);

-- 더미 데이터 삽입

INSERT INTO UserAccount (account_code, account_type, account_status) VALUES
('U001', 'PREMIUM', 'ACTIVE'),
('U002', 'STANDARD', 'ACTIVE');

INSERT INTO UserProfile (profile_code, user_name) VALUES
('U001', 'Alice'),
('U002', 'Bob');

INSERT INTO UserActivity (user_code, action_code, action_date, action_type, interaction_count) VALUES
('U001', 'A001', '2024-01-01', 'CLICK', 100),
('U001', 'A002', '2024-01-02', 'CLICK', 150),
('U002', 'A003', '2024-01-03', 'CLICK', 200);

 

QueryDSL로 출력시

fun findUserActivityByMonth(
    date: YearMonth,
    actionCodes: List<String>,
    actionType: String
): List<UserActivityDTO> {
    val start = date.atDay(1)
    val end = date.atEndOfMonth()

    val userActivity = QUserActivity.userActivity
    val userAccount = QUserAccount.userAccount
    val userProfile = QUserProfile.userProfile

    return queryFactory
        .from(userActivity)
        .join(userAccount)
            .on(userActivity.userCode.eq(userAccount.accountCode),
                userAccount.accountType.eq("PREMIUM"),
                userAccount.accountStatus.eq("ACTIVE"))
        .join(userProfile)
            .on(userAccount.accountCode.eq(userProfile.profileCode))
        .where(
            userActivity.actionDate.between(start, end),
            userActivity.actionType.eq(actionType),
            userActivity.actionCode.`in`(actionCodes)
        )
        .groupBy(userAccount.accountId, userAccount.accountCode, userProfile.userName)
        .orderBy(userAccount.accountId.asc())
        .transform(GroupBy.groupBy(userAccount.accountId).list(
            Projections.constructor(
                UserActivityDTO::class.java,
                userAccount.accountId,
                userAccount.accountCode,
                userProfile.userName,
                GroupBy.list(
                    Projections.constructor(
                        UserActivityDTO.ActionDetail::class.java,
                        userActivity.actionCode,
                        userActivity.interactionCount.sum()
                    )
                )
            )
        ))
}