I’m currently working with QueryDSL in a Java application, and I’m trying to generate a specific SQL query. However, the QueryDSL code I’ve written is producing a different SQL query than what I need.

Here’s the SQL query that I want to generate:

SELECT DISTINCT
    student.id,
    semester.id,
    module.eid,
    CASE
        WHEN (special_permission.id IS NOT NULL) THEN true
        ELSE false
    END
FROM registration
JOIN event ON registration.event_id = event.id
JOIN student ON registration.student_id = student.id
JOIN semester ON event.semester_id = semester.id
JOIN course_template ON event.course_template_id = course_template.id
JOIN module_course_template ON course_template.id = module_course_template.course_template_id
JOIN module ON module_course_template.module_id = module.id
LEFT OUTER JOIN special_permission ON special_permission.semester_id = 99
    AND special_permission.student_id = student.id
WHERE student.archive_timestamp IS NULL
AND semester.id = 99
GROUP BY student.id, semester.id, module.eid, special_permission.id;

However, my current QueryDSL code is generating the following SQL query:

SELECT DISTINCT 
    student.id AS student_id, 
    semester.id AS semester_id, 
    module.eid AS module_eid, 
    CASE 
        WHEN (special_permission.id IS NOT NULL) THEN true 
        ELSE false 
    END
FROM 
    registration
    LEFT OUTER JOIN special_permission ON (special_permission.semester_id = 99 AND student.id = student.id),
    student,
    module_course_template,
    course_template,
    module,
    event,
    semester
WHERE 
    NOT (student.archive_timestamp IS NOT NULL)
    AND event.semester_id = 99
    AND student.id = registration.student_id
    AND event.id = registration.event_id
    AND semester.id = event.semester_id
    AND course_template.id = event.course_template_id
    AND module_course_template.course_template_id = course_template.id
    AND module.id = module_course_template.module_id
    AND student.id = special_permission.student_id
GROUP BY 
    student.id, 
    semester.id, 
    module.eid, 
    special_permission.id;

Here is the QueryDSL code I’m currently using:

public List<ModuleRegistrationDto> findAllAsDTO(Semester semester, Pagination pagination) {
    QStudent qStudent = QStudent.student;
    QSemester qSemester = QSemester.semester;
    QModule qModule = QModule.module;
    QSpecialPermission qSpecialPermission = QSpecialPermission.specialPermission;
    QEvent qEvent = QEvent.event;
    QCourseTemplate qCourseTemplate = QCourseTemplate.courseTemplate;

    return new JPAQuery<>(em)
            .select(new QModuleRegistrationDto(
                    qStudent.id,
                    qSemester.id,
                    qModule.eid,
                    new CaseBuilder()
                            .when(qSpecialPermission.id.isNotNull()).then(true)
                            .otherwise(false)
            ))
            .from(q) // registration table
            .join(q.event, qEvent)
            .join(q.student, qStudent)
            .join(qEvent.semester, qSemester)
            .join(qEvent.courseTemplate, qCourseTemplate)
            .join(qCourseTemplate.moduleList, qModule)
            .leftJoin(qSpecialPermission)
            .on(qSpecialPermission.semester.eq(semester)
                    .and(qSpecialPermission.student.id.eq(qStudent.id)))
            .where(isNotArchived(qStudent.archiveInfo), qSemester.eq(semester))
            .groupBy(qStudent.id, qSemester.id, qModule.eid, qSpecialPermission.id)
            .distinct().fetch();
}

q refres to the registration.

How can I modify my QueryDSL code to generate the desired SQL query?

Thank you for your help!