new_query.sql

SELECT data.id
FROM (select j1.id                                                    as id,
             j1.title,
             case
               when active_job.location isnull
                 then :radius
               when j1.location isnull
                 then :radius
               else st_distance(j1.location, active_job.location) end as dist,
             similarity(j1.title, active_job.title)                   as close,
             active_job.title
      from similar_mv j1,
           (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 = :jobid) active_job
      where (j1.source != active_job.source or
             (j1.source = 'JobNinja' and active_job.company != j1.company))
        and j1.id != active_job.id
        and j1.category_id = active_job.category_id
        and st_dwithin(j1.location, active_job.location, :radius)
      order by close desc, dist, j1.updated_at desc) data
where data.close > similarity
order by data.close desc
limit :count;