I wrote this query which is a little bit complex. The goal is to fetch the records which match any of these
from(rx in Rx, left_join: recert in RxCert, on: rx.id == recert.rx_id, where: is_nil(rx.date_end) or fragment("?::date - ?::date", ^Timex.today(), rx.date_end) > ^@expiry_duration or fragment("?::date - (SELECT MAX(start_date) FROM rx_recerts )::date", ^Timex.today()) > ^@expiry_duration, preload: [certs: :prescribing_clinician], distinct: true )
The issue I am facing is with the
SELECT MAX. My goal is to get maximum value from the
start_date of the joining table
rx_recerts, but instead it just looks the entire rx_recerts table and selects the maximum start_date. How can I update this query to
select MAX(start_date) from the joining records of rx_recerts and not the entire recert table.