Fetching Email Threads in Veure

gistfile1.sql

   SELECT thread.*
     FROM email        thread
     JOIN email        selected    ON selected.email_thread_id = thread.email_thread_id
     JOIN character    recipient   ON recipient.character_id   = thread.recipient_id
     JOIN station_area sa          ON sa.station_area_id       = recipient.station_area_id
     JOIN station      st          ON st.station_id            = sa.station_id
     JOIN star         origin      ON origin.star_id           = thread.sender_star_id
     JOIN star         destination ON destination.star_id      = st.star_id
LEFT JOIN route
       ON ( route.from_id = origin.star_id AND route.to_id = destination.star_id )
    WHERE selected.email_id          = ?
      AND ( thread.sender_id         = ? 
            OR ( thread.recipient_id = ?
                 AND ( origin.star_id = destination.star_id
                       OR ( route.distance IS NOT NULL
                            AND
                            now() >= thread.datesent + ( route.distance * interval '30 seconds' )
                       )
                 )
            )
      )
ORDER BY datesent ASC, parent_id ASC NULLS FIRST