2

I use Spring Boot with Hibernate. Currently I have 3 separate requests to a database:

  1. fetch all specific (with some WHERE conditions) data from table aaa
  2. fetch all specific (with some WHERE conditions) data from table bbb
  3. fetch max date of record that is found by WHERE clause with the same conditions from points 1 and 2.

Statement #1

SELECT count(a.id) as dateTo
from (
        SELECT a.date_to
        FROM aaa a
                 JOIN ramp r on a.ramp_id = r.id
                 JOIN warehouse w on r.warehouse_id = r.warehouse_id
        WHERE w.id = 222
          AND a.date_from >= '2022-08-20T00:00'      
    ) allDates

Statement #2

SELECT count(b.id) as dateTo
from (
        SELECT b.date_to
        FROM bbb b
        WHERE tw.warehouse.id = :warehouseId
            AND tw.status = 'AVAILABLE'
    ) allDates

Statement #3

SELECT MAX(date_to) as dateTo
from (
        SELECT a.date_to
        FROM aaa a
                 JOIN ramp r on a.ramp_id = r.id
                 JOIN warehouse w on r.warehouse_id = r.warehouse_id
        WHERE w.id = 222
          AND a.date_from >= '2022-08-20T00:00'      
        UNION
        SELECT b.valid_to as date_to
        FROM bbb b
        WHERE b.warehouse_id = 222
          AND tw.status = 'AVAILABLE'
    ) allDates

Is it possible to do all this with one statement? I use MySql 5.7 so CTE is not available.

My code in Spring:

final long numberOfa = ...
final long numberOfB = ...
final LocalDate maxDate = ...

Expected result:

final MyObjectWithAllThreeValues myObject = repository.getAllDataWithOneQuery
Matley
  • 1,953
  • 4
  • 35
  • 73

2 Answers2

0

You can store a flag in the subqueries (called "which_tab"), then use a CASE expression within a SUM aggregation function to count your rows.

SELECT MAX(date_to) AS dateTo,
       SUM(CASE WHEN which_tab = 'a' THEN 1 END) AS count_a_id,
       SUM(CASE WHEN which_tab = 'b' THEN 1 END) AS count_b_id
from (
        SELECT 'a' AS which_tab, a.date_to
        FROM aaa a
                 JOIN ramp r on a.ramp_id = r.id
                 JOIN warehouse w on r.warehouse_id = r.warehouse_id
        WHERE w.id = 222
          AND a.date_from >= '2022-08-20T00:00'      
        UNION ALL
        SELECT 'b' AS which_tab, b.valid_to AS date_to
        FROM bbb b
        WHERE b.warehouse_id = 222
          AND tw.status = 'AVAILABLE'
    ) allDates

Note: if your rows from the two subqueries you apply the UNION on do not overlap, it's better to use UNION ALL as it avoids an additional unnecessary aggregation.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • but how can I use it in my Spring app? I think its' impossible to use casting like select new MyObjectToBeCast(max(date_to), sum(....) – Matley Aug 21 '22 at 12:50
  • Hmm so maybe I should use projection: https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo – Matley Aug 21 '22 at 13:00
  • This is the query that merges your three queries in MySQL 5.7, without using ctes. Unfortunately I've never used spring, hopefully there's a way to do it. – lemon Aug 21 '22 at 13:03
0

All queries return a single row, because you aggregate all rows without any GROUP BY. You can hence do both aggregations and then cross join the two result rows:

SELECT
  a_agg.cnt AS count_a,
  b_agg.cnt AS count_b,
  GREATEST(a.max_date, b.max_date) AS max_date
FROM 
(
  SELECT COUNT(*) AS cnt, MAX(a.date_to) AS max_date
  FROM aaa a
  JOIN ramp r ON r.id = a.ramp_id
  JOIN warehouse w ON w.warehouse_id = r.warehouse_id
  WHERE w.id = 222
  AND a.date_from >= TIMESTAMP '2022-08-20 00:00:00'      
) a_agg
CROSS JOIN
(
  SELECT COUNT(*) AS cnt, MAX(b.date_to) AS max_date
  FROM bbb b
  WHERE b.warehouse.id = :warehouseId
  AND b.status = 'AVAILABLE'
) b_agg;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73