Checking for an empty subquery in Join
I have an event table with a non-empty date field. Based on the summary date and offset, the query should return the following 2 events.
Here's the query I'm trying to build:
SELECT e.* FROM Events as e
JOIN (
-- latest event before pivot date, with offset
SELECT * FROM Events where date < :dateRef
ORDER BY date DESC, id DESC LIMIT :offset,1
) as eLatest
-- depending on :dateRef and :offset, eLatest can be empty!
-- If eLatest is empty, the join should produce all rows from Events table.
-- "eLatest is null" gives an error... How can I do it?
ON e.date > eFirst.date || (e.date = eFirst.date && e.id >= eFirst.id)
ORDER BY e.date, e.id LIMIT 2
On the following sequence of events when sorting by date ASC (no equal dates to keep it simple): e0 - e1 - e2 -... - e10
here are some desired results:
- : dateRef = e2.date ,: offset = 0: [e1, e2]
- : dateRef = e2.date ,: offset = 1: [e0, e1]
- : dateRef = e2.date ,: offset> 1: [e0, e1] <--- Doesn't work, doesn't return a string
- : dateRef <= e0.date ,: offset = ?: [e0, e1] <--- Doesn't work, doesn't return a string
- : dateRef = e5.date ,: offset = 2: [e2, e3]
- : dateRef> e10.date ,: offset = 0: [e10]
- : dateRef> e10.date ,: offset = 2: [e8, e9]
The problem is that when the subquery is eLatest
empty, the connection never matches. I can't figure out how to remove the join condition when eLatest
empty.
The final query must be executed in JPQL or JPA criteria (so no UNIONS! I already have a query that works with UNION and am trying to get rid of it ...)
source to share
Will the RIGHT JOIN solution do?
SELECT e.* FROM Events as e
JOIN (
-- latest event before pivot date, with offset
SELECT COALESCE(ev.date, b.date), COALESCE(ev.id, b.id)
FROM (
SELECT * FROM Events where date < :dateRef
ORDER BY date DESC, id DESC LIMIT :offset,1
) ev
RIGHT JOIN (
SELECT CAST('1900-01-01' AS date) AS date, 0 AS id
) AS b ON 1=1
) as eFirst
ON e.date > eFirst.date || (e.date = eFirst.date && e.id >= eFirst.id)
ORDER BY e.date, e.id LIMIT 2
source to share