Legacy Query

legacy.sql

SELECT data.id
FROM (select j1.id                                                    as id,
             j1.title,
             b1.company,
             case
               when active_job.location isnull
                 then :radius
               when l1.location isnull
                 then :radius
               else st_distance(l1.location, active_job.location) end as dist,
             l1.city,
             similarity(j1.title, active_job.title)                   as close,
             active_job.title
      from job_tb j1
             join b2b_tb b1 on b1.id = j1.b2b_id
             left outer join location_tb l1 on j1.location_id = l1.id,
           (select j.id        as id,
                   title       as title,
                   location    as location,
                   source      as source,
                   category_id as category_id,
                   company     as company
            from job_tb j
                   join b2b_tb b on j.b2b_id = b.id
                   left outer join location_tb l on j.location_id = l.id
            where j.id = :job_id) active_job
      where (j1.source not in ('Free sources'))
        and j1.finished != true
        and l1.addr_zip notnull
        and j1.id != active_job.id
        and j1.category_id = active_job.category_id
      order by close desc, dist, j1.updated_at desc) data
where dist <= :radius
  and data.close > similarity
order by data.close desc
limit :count