I am trying to achieve something like this with KnexJS:
WITH first AS (SELECT ...etc...),
second AS (SELECT ...etc... WHERE NOT EXISTS (TABLE first))
SELECT * FROM first
UNION ALL
SELECT * FROM second
Can it be done with KnexJS?
Knex provides a .with method but I see no information about how I can achieve multiple WITH as in the statement above. Likewise there is a .unionAll method, but how to mix it with .with is not clear.
The exact statement I am after (for postgresql) is:
WITH first AS (
SELECT results.simulation_id, simulation_time, variable, value, runset_id, (case WHEN ended_at is null then 'INCOMPLETE' else 'COMPLETE' END) as status FROM results
JOIN bookkeeping as bk ON bk.simulation_id = results.simulation_id
WHERE bk.simulation_id=?
AND variable=?
ORDER BY simulation_time
),
second AS (
SELECT simulation_id, null::real as simulation_time, null as variable, null::real as value, null as runset_id, (case WHEN ended_at is null then 'INCOMPLETE' else 'COMPLETE' END) as status FROM bookkeeping
WHERE NOT EXISTS (TABLE first)
AND simulation_id=?
)
SELECT * FROM first
UNION ALL
SELECT * FROM second;