Fragment to get maximum value from a joining table field

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: == recert.rx_id,
    where: is_nil(rx.date_end) or fragment("?::date - ?::date", ^, rx.date_end) > ^@expiry_duration or fragment("?::date - (SELECT MAX(start_date) FROM rx_recerts
    )::date", ^ > ^@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.

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