Combining two JPA Spring query SQL queries

Question

I created 2 custom SQL queries to get my data from my PostgreSQL database. I made two queries with on minor change. The user ID part. with wd.id it returns all worker reports, but sometimes I need to get a specific worker report. Is there any way to combine the two queries?

@Query(value = "SELECT\n" +
            "\twd.id as workDetailId,\n" +
            "    max(ua.name) as name,\n" +
            "    count(sw.id) as shiftCount,\n" +
            "    sum(sw.actual_work_duration) as workDuration,\n" +
            "    sum(sw.actual_shift_duration) as shiftDuration,\n" +
            "\twd.salary as salary,\n" +
            "\tmax(mi.name) as jobRelation\n" +
            "FROM shift_worker sw\n" +
            "JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_id\n" +
            "JOIN work_detail wd ON sw.work_detail_id = wd.id\n" +
            "JOIN maintenance_item mi ON wd.job_relation_id = mi.id\n" +
            "WHERE sw.work_detail_id = wd.id AND\n" +
            "    sw.actual_work_start_time > ?1 AND\n" +
            "    sw.actual_work_start_time < ?2 AND sw.actual_work_end_time IS NOT NULL\n" +
            "GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReports(LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

@Query(value = "SELECT\n" +
            "\twd.id as workDetailId,\n" +
            "    max(ua.name) as name,\n" +
            "    count(sw.id) as shiftCount,\n" +
            "    sum(sw.actual_work_duration) as workDuration,\n" +
            "    sum(sw.actual_shift_duration) as shiftDuration,\n" +
            "\twd.salary as salary,\n" +
            "\tmax(mi.name) as jobRelation\n" +
            "FROM shift_worker sw\n" +
            "JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_id\n" +
            "JOIN work_detail wd ON sw.work_detail_id = wd.id\n" +
            "JOIN maintenance_item mi ON wd.job_relation_id = mi.id\n" +
            "WHERE sw.work_detail_id = ?1 AND\n" +
            "    sw.actual_work_start_time > ?2 AND\n" +
            "    sw.actual_work_start_time < ?3 AND sw.actual_work_end_time IS NOT NULL\n" +
            "GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReportByUserId(Long userId, LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

S B · Accepted Answer · 2019-11-12 05:00:24Z

1

If you could merge the two methods keeping the userId parameter, pass a 0 userId where the you want generic results and assuming there will be no data with userId equal to 0, you can combine the queries as -

Where sw.work_detail_id = case when ?1 = 0 then wd.id else ?1 end

Essentially a "case when" syntax usage on the differing column in where clause in your queries.

answered Nov 12, 2019 at 5:00

S B

3942 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining two JPA Spring query SQL queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related