Skip to content

Instantly share code, notes, and snippets.

@lolobosse
Created February 20, 2021 11:50
Show Gist options
  • Save lolobosse/17c442f8272f096bece9a44c941a65a8 to your computer and use it in GitHub Desktop.
Save lolobosse/17c442f8272f096bece9a44c941a65a8 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment