Употреба на напредно кверирање во базата

Следното парче код е познато како „градење на спецификација“, која во JAVA светот е во суштина некакво си генеричко квери кое се извршува во позадината. Наместо да се изгради со SQL, се гради со Specification објект, кој потоа се преведува во SQL квери и враќа резултат од истото. Ќе го покажам тоа со неколку различни случаи.

Идејата е да се овозможи напредно филтрирање на извештаи од различен тип.

public static Specification<Report> build(ReportFilterDTO filter) {
        return (root, query, cb) -> {
            query.distinct(false);
            LocalDate now = LocalDate.now();
            List<Predicate> predicates = new ArrayList<>();
            Join<Report, Person> report_on_person_join = root.join("person", JoinType.LEFT);

            /* Person */
            if (filter.getFilter_selected().equals(SelectedFilterSection.PERSON)) {
                // predicate that checks if there exists a person with the string provided in the name
                if (filter.getPerson_name_string() != null && !filter.getPerson_name_string().isBlank()) {
                    predicates.add(cb.like(cb.lower(report_on_person_join.get("name")),
                            "%" + filter.getPerson_name_string().toLowerCase() + "%"));
                }
                // predicate that checks if there exists a person with the string provided in the surname
                if (filter.getPerson_surname_string() != null && !filter.getPerson_surname_string().isBlank()) {
                    predicates.add(cb.like(cb.lower(report_on_person_join.get("surname")), "%" + filter.getPerson_surname_string().toLowerCase() + "%"));
                }
                // Age filters work for people who are alive only, otherwise no
                if (filter.getIs_alive()) {
                    if (filter.getCorrect_age() != null && filter.getCorrect_age() > 0) {
                        Expression<Integer> years_age = cb.function("date_part", Integer.class, cb.literal("year"),
                                cb.function("age", String.class, cb.currentDate(), report_on_person_join.get("dateOfBirth")));
                        predicates.add(cb.equal(years_age, filter.getCorrect_age()));
                    } else if (filter.getCorrect_age() == null || filter.getCorrect_age() == 0) {
                        if (filter.getAge_start() > 0) {
                            LocalDate start_age = now.minusYears(filter.getAge_start());//
                            predicates.add(cb.lessThanOrEqualTo(report_on_person_join.get("dateOfBirth"), start_age));
                        }
                        if (filter.getAge_end() < 120) {
                            LocalDate end_age = now.minusYears(filter.getAge_end() + 1).plusDays(1);
                            predicates.add(cb.greaterThanOrEqualTo(report_on_person_join.get("dateOfBirth"), end_age));
                        }
                    }
                    predicates.add(cb.isNull(report_on_person_join.get("dateOfDeath")));
                } else {
                    predicates.add(cb.isNotNull(report_on_person_join.get("dateOfDeath")));
                }
                // Gender
                if (filter.getGender() != null) {
                    predicates.add(cb.equal(report_on_person_join.get("gender"), filter.getGender()));
                }
                // Address
                if (filter.getAddress_string() != null && !filter.getAddress_string().isBlank()) {
                    predicates.add(cb.like(
                            cb.lower(report_on_person_join.get("address")),
                            "%" + filter.getAddress_string().toLowerCase() + "%"
                    ));
                }
            }

            /* Employment Report */
            //ALL TESTED!
            if (filter.getFilter_selected().equals(SelectedFilterSection.EMPLOYMENT)) {
                Join<Report, EmploymentReport> employment_report_join = root.join("employmentReport", JoinType.LEFT);
                //predicate for income check (more,less,equal)
                if (filter.getIncome_comparison() != null && filter.getIncome_amount() > 0) {
                    switch (filter.getIncome_comparison()) {
                        case more ->
                                predicates.add(cb.greaterThan(employment_report_join.get("incomePerMonth"), filter.getIncome_amount()));
                        case equal ->
                                predicates.add(cb.equal(employment_report_join.get("incomePerMonth"), filter.getIncome_amount()));
                        case less ->
                                predicates.add(cb.lessThan(employment_report_join.get("incomePerMonth"), filter.getIncome_amount()));
                    }
                }
                //predicate for years_experience check (more,less,equal)
                if (filter.getYears_experience_comparison() != null && filter.getYears_experience() > 0) {
                    //this function is in the database, and I execute it
                    Expression<Integer> totalYearsExpr = cb.function(
                            "years_total", Integer.class,
                            employment_report_join.get("startDate"), employment_report_join.get("endDate")
                    );
                    switch (filter.getYears_experience_comparison()) {
                        case more -> predicates.add(cb.greaterThan(totalYearsExpr, filter.getYears_experience()));
                        case equal -> predicates.add(cb.equal(totalYearsExpr, filter.getYears_experience()));
                        case less -> predicates.add(cb.lessThan(totalYearsExpr, filter.getYears_experience()));
                    }
                }
            }

            /* Academic Report */
            //ALL TESTED!
            if (filter.getFilter_selected().equals(SelectedFilterSection.ACADEMIC)) {
                Join<Report, AcademicReport> academic_report_join = root.join("academicReport", JoinType.LEFT);
                //predicate for field of study
                if (filter.getAcademic_field() != null && !filter.getAcademic_field().isBlank()) {
                    predicates.add(cb.like(cb.lower(academic_report_join.get("academicField")), "%" + filter.getAcademic_field().toLowerCase() + "%"));
                }
                //predicate for institution field
                Join<AcademicReport, Institution> academic_report_institution_join = academic_report_join.join("institution", JoinType.LEFT);
                if (filter.getInstitution_type() != null) {
                    predicates.add(cb.equal(academic_report_institution_join.get("type"), filter.getInstitution_type()));
                }
            }

            /* Medical Report */
            //ALL TESTED!
            if (filter.getFilter_selected().equals(SelectedFilterSection.MEDICAL)) {
                // Join to MedicalReport and Doctor
                Join<Report, MedicalReport> medicalReportJoin = root.join("medicalReport", JoinType.LEFT);
                Join<MedicalReport, Doctor> doctorJoin = medicalReportJoin.join("doctor", JoinType.LEFT);
                Join<MedicalReport, MedicalReportDiagnosis> diagnosisLinkJoin = medicalReportJoin.join("medicalReportDiagnoses", JoinType.LEFT);
                Join<MedicalReportDiagnosis, Diagnosis> diagnosisJoin = diagnosisLinkJoin.join("diagnosis", JoinType.LEFT);
                // Has Next Medical Control
                if (Boolean.TRUE.equals(filter.getHas_next_control())) {
                    predicates.add(cb.isNotNull(medicalReportJoin.get("nextControlDate")));
                } else {
                    predicates.add(cb.isNull(medicalReportJoin.get("nextControlDate")));
                }
                // Doctor Name
                if (filter.getDoctor_name_string() != null && !filter.getDoctor_name_string().isBlank()) {
                    predicates.add(cb.like(cb.lower(doctorJoin.get("name")), "%" + filter.getDoctor_name_string().toLowerCase() + "%"));
                }
                // Doctor Surname
                if (filter.getDoctor_surname_string() != null && !filter.getDoctor_surname_string().isBlank()) {
                    predicates.add(cb.like(cb.lower(doctorJoin.get("surname")), "%" + filter.getDoctor_surname_string().toLowerCase() + "%"));
                }
                // Specialization
                if (filter.getSpecialization() != null) {
                    predicates.add(cb.equal(doctorJoin.get("specialization"), filter.getSpecialization()));
                }
                // Chronic diagnosis
                if (filter.getIs_chronic() != null) {
                    predicates.add(cb.equal(diagnosisJoin.get("isChronic"), filter.getIs_chronic()));
                }
            }
            
            /* Criminal Report */
            if (filter.getFilter_selected().equals(SelectedFilterSection.CRIMINAL)) {
                Join<Report, CriminalReport> criminal_report_join = root.join("criminalReport", JoinType.LEFT);
                Join<CriminalReport, CrimeType> crime_type_join = criminal_report_join.join("crimeType", JoinType.LEFT);
                //predicate for severity level
                if (filter.getCrime_severity_level() != null) {
                    predicates.add(cb.equal(crime_type_join.get("severityLevel"), filter.getCrime_severity_level()));
                }
                //predicate for resolved
                if (filter.getIs_resolved() != null) {
                    predicates.add(cb.equal(criminal_report_join.get("resolved"), filter.getIs_resolved()));
                }
                Join<CriminalReport, Punishment> punishment_join = criminal_report_join.join("punishment", JoinType.INNER);
                //predicate for punishment as fine
                if (PunishmentType.FINE.equals(filter.getPunishment_type()) && filter.getPunishment_fine() != null && filter.getPunishment_fine() > 0) {
                    predicates.add(cb.equal(punishment_join.get("fineToPay"), filter.getPunishment_fine()));
                }
                //predicate for punishment as prison
                if (PunishmentType.PRISON.equals(filter.getPunishment_type()) && filter.getPunishment_years() != null && filter.getPunishment_years() > 0) {
                    Expression<LocalDate> created_at = root.get("createdAt");
                    Expression<Integer> years_in_prison = cb.function("years_total", Integer.class,
                            created_at, punishment_join.get("releaseDate"));
                    predicates.add(cb.equal(years_in_prison, filter.getPunishment_years()));
                }
                //predicate for criminal type
                if (filter.getCrime_type_label() != null && !filter.getCrime_type_label().isBlank()) {
                    predicates.add(cb.like(cb.lower(crime_type_join.get("label")), "%" + filter.getCrime_type_label().toLowerCase() + "%"));
                }
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }

Подолу се прикажани кверињата кои во позадината се извршуваат за да се постигне филтрирањето, за секоја опција на филтрирање посебно.

PERSON избор

SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
FROM report r
LEFT JOIN person p ON p.person_id = r.person_id
WHERE 1=1
  /* name contains */
  AND (:namePattern IS NULL OR LOWER(p.name) LIKE :namePattern)
  /* surname contains */
  AND (:surnamePattern IS NULL OR LOWER(p.surname) LIKE :surnamePattern)
  /* alive / not alive */
  AND (
        (:isAlive = TRUE  AND p.date_of_death IS NULL)
     OR (:isAlive = FALSE AND p.date_of_death IS NOT NULL)
  )
  /* exact age (years) using Postgres functionalities */
  AND (
        :correctAge IS NULL OR :correctAge = 0 OR
        date_part('year', age(current_date, p.date_of_birth)) = :correctAge
  )
  /* age range expressed via date_of_birth bounds (computed in app, with function) */
  AND (:dobMax IS NULL OR p.date_of_birth <= :dobMax)
  AND (:dobMin IS NULL OR p.date_of_birth >= :dobMin)
  /* gender equals */
  AND (:gender IS NULL OR p.gender = :gender)
  /* address contains */
  AND (:addrPattern IS NULL OR LOWER(p.address) LIKE :addrPattern);

ACADEMIC избор

SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
FROM report r
LEFT JOIN academicreport ar ON ar.report_id = r.report_id
LEFT JOIN institution i ON i.institution_id = ar.institution_id
WHERE 1=1
  /* academic field contains */
  AND (:fieldPattern IS NULL OR LOWER(ar.academic_field) LIKE :fieldPattern)
  /* institution type equals */
  AND (:instType IS NULL OR i.type = :instType);

CRIMINAL избор

SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
FROM report r
LEFT JOIN criminalreport cr ON cr.report_id = r.report_id
LEFT JOIN crimetype ct ON ct.crime_type_id = cr.crime_type_id
JOIN punishment p ON p.report_id = cr.report_id
WHERE 1=1
  /* severity level equals */
  AND (:severityLevel IS NULL OR ct.severity_level = :severityLevel)
  /* resolved flag equals */
  AND (:isResolved IS NULL OR cr.resolved = :isResolved)
  /* fine (applies when punishment_type = FINE) */
  AND (:punType <> 'FINE' OR :fineAmount IS NULL OR p.fine_to_pay = :fineAmount)
  /* prison years via years_total(created_at, release_date) (applies when punishment_type = PRISON) */
  AND (:punType <> 'PRISON' OR :prisonYears IS NULL OR :prisonYears <= 0
       OR years_total(r.created_at, p.release_date) = :prisonYears)
  /* crime type label contains */
  AND (:crimeLabelPattern IS NULL OR LOWER(ct.label) LIKE :crimeLabelPattern);

MEDICAL избор

SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
FROM report r
LEFT JOIN medicalreport mr ON mr.report_id = r.report_id
LEFT JOIN doctor d         ON d.doctor_id = mr.doctor_id
LEFT JOIN medicalreport_diagnosis mrd ON mrd.report_id = mr.report_id
LEFT JOIN diagnosis diag   ON diag.diagnosis_id = mrd.diagnosis_id
WHERE 1=1
  /* next control presence (choose exactly one branch in code) */
  AND (
        (:hasNextControl = TRUE  AND mr.next_control_date IS NOT NULL) OR
        (:hasNextControl = FALSE AND mr.next_control_date IS NULL) OR
        (:hasNextControl IS NULL)
      )
  /* doctor name/surname contains */
  AND (:docNamePattern    IS NULL OR LOWER(d.name)    LIKE :docNamePattern)
  AND (:docSurnamePattern IS NULL OR LOWER(d.surname) LIKE :docSurnamePattern)
  /* specialization equals */
  AND (:specialization IS NULL OR d.specialization = :specialization)
  /* chronic flag equals */
  AND (:isChronic IS NULL OR diag.is_chronic = :isChronic);

EMPLOYMENT избор

SELECT r.report_id, r.created_at, r.person_id, r.report_type, r.summary
FROM report r
LEFT JOIN employmentreport e ON e.report_id = r.report_id
WHERE 1=1
  /* income comparison: more/equal/less (include only one branch) */
  AND (
       (:incomeCmp = 'more'  AND e.income_per_month >  :incomeAmount) OR
       (:incomeCmp = 'equal' AND e.income_per_month =  :incomeAmount) OR
       (:incomeCmp = 'less'  AND e.income_per_month <  :incomeAmount) OR
       (:incomeCmp IS NULL)
  )
  /* years_total(start_date,end_date) custom DB function */
  AND (
       :yearsCmp IS NULL OR :yearsValue IS NULL OR :yearsValue <= 0 OR
       (
         (:yearsCmp = 'more'  AND years_total(e.start_date, e.end_date) >  :yearsValue) OR
         (:yearsCmp = 'equal' AND years_total(e.start_date, e.end_date) =  :yearsValue) OR
         (:yearsCmp = 'less'  AND years_total(e.start_date, e.end_date) <  :yearsValue)
       )
  );

Квери кое ги влече пациентите со слични дијагнози

with selected_person_diagnosis as(
    select distinct d.diagnosis_id as diagnosis_id, d.short_description as label
    from person p
             join report r on r.person_id = p.person_id
             join medicalreport_diagnosis mrd on mrd.report_id = r.report_id
             join diagnosis d on mrd.diagnosis_id = d.diagnosis_id
    where p.person_id = :person_id
)
select cast(p2.person_id as bigint),
       p2.name || ' ' || p2.surname as full_name,
       cast(count(distinct spd.diagnosis_id) as bigint) as matching_diagnoses_count,
       string_agg(distinct spd.label, ', ') as matching_labels
from selected_person_diagnosis spd
         join medicalreport_diagnosis mrd2 on mrd2.diagnosis_id = spd.diagnosis_id
         join report r2 on r2.report_id = mrd2.report_id
         join person p2 on p2.person_id = r2.person_id
where p2.person_id != :person_id
group by p2.person_id, p2.name, p2.surname
having count(distinct spd.diagnosis_id) >=1
order by matching_diagnoses_count desc;

Пребарување на најдобрите 3 институции во изминатиот период од 1 година

WITH top_3_institutions AS (
  SELECT ar.institution_id, COUNT(*) AS total_reports
  FROM report r
  JOIN academicreport ar ON ar.report_id = r.report_id
  WHERE r.created_at >= date_trunc('year', now()) - interval '1 year'
  GROUP BY ar.institution_id
  ORDER BY COUNT(*) DESC
  LIMIT 3
)
SELECT i.name, a.total_reports
FROM top_3_institutions a
JOIN institution i ON i.institution_id = a.institution_id
ORDER BY a.total_reports DESC;