Fragment to get maximum value from a joining table field

Questions / Help
1

I wrote this query which is a little bit complex. The goal is to fetch the records which match any of these Where conditions.

   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.
Thanks

2

You can do a subquery or a AND-statement in your left join: