Skip to content

Instantly share code, notes, and snippets.

@lolobosse
Created Feb 20, 2021
Embed
What would you like to do?
Legacy Query
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment