Something went wrong. Try again.
find work, anywhere www.remotehiro.com
rust job-board
Something went wrong. Try again.
3.8 kB · 129 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130-- Deploy remotehiro:20260522210751_update_view to sqlite
BEGIN IMMEDIATE;
DROP VIEW enriched_jobs;
CREATE VIEW enriched_jobs AS SELECT -- Basic job details j.job_id, j.position, j.description, j.apply_url, j.apply_email, j.post_url, j.source, j.min_equity, j.max_equity,
-- Employment type j.employment_type_id, et.name AS employment_type,
-- Location requirements
--- If this is true, then it is assumed that the job is fully remote, and --- neither hybrid or on-site. is_remote,
--- Job location requirements jl.region_id AS job_region_id, jl.subregion_id AS job_subregion_id, jl.country_id AS job_country_id, jl.state_id AS job_state_id, jl.city_id AS job_city_id,
--- Applicant location requirements jal.region_id AS applicant_region_id, jal.business_region_id AS applicant_business_region_id, jal.subregion_id AS applicant_subregion_id, jal.country_id AS applicant_country_id, jal.state_id AS applicant_state_id, jal.city_id AS applicant_city_id,
-- Job discoverability --- Categories ca.category_id, ca.name AS category_name,
-- Company information j.company_id, co.name AS company_name,
-- Salary details c.currency_id, c.code AS currency_code, c.symbol AS currency_symbol, jls.job_location_salary_id, jls.region_id AS salary_region_id, sr.name AS salary_region_name, salary_business_regions.business_region_id AS salary_business_region_id, salary_business_regions.name AS salary_business_region_name, salary_business_regions.iso2 AS salary_business_region_iso2, salary_business_regions.emoji AS salary_business_region_flag, jls.subregion_id AS salary_subregion_id, ssr.name AS salary_subregion_name, jls.country_id AS salary_country_id, sco.name AS salary_country_name, sco.emoji AS salary_country_flag, jls.state_id AS salary_state_id, sst.name AS salary_state_name, jls.city_id AS salary_city_id, sci.name AS salary_city_name, jls.min_salary, jls.max_salary,
-- Add-ons j.addon_pinned_until, j.addon_basic_highlight, substr( j.addon_custom_highlight, 2, length(j.addon_custom_highlight) ) AS addon_custom_highlight,
-- Addons: check if the job post is still pinned coalesce( current_timestamp < j.addon_pinned_until, FALSE ) AS is_pinned,
(julianday(current_timestamp) - julianday(j.created_at)) * 86400.00 AS relative_created_at, (julianday(current_timestamp) - julianday(j.bumped_at)) * 86400.00 AS relative_bumped_at, j.created_at, j.updated_at, j.bumped_at, j.deleted_at FROM jobs j LEFT JOIN jobs_locations jl ON j.job_id = jl.job_id LEFT JOIN jobs_applicant_locations jal ON j.job_id = jal.job_id LEFT JOIN companies co ON j.company_id = co.company_id LEFT JOIN categories ca ON j.category_id = ca.category_id LEFT JOIN employment_types et ON j.employment_type_id = et.employment_type_id LEFT JOIN jobs_location_salaries jls ON j.job_id = jls.job_id LEFT JOIN currencies c ON jls.currency_id = c.currency_id LEFT JOIN regions sr ON jls.region_id = sr.region_id LEFT JOIN subregions ssr ON jls.subregion_id = ssr.subregion_id LEFT JOIN countries sco ON jls.country_id = sco.country_id LEFT JOIN states sst ON jls.state_id = sst.state_id LEFT JOIN cities sci ON jls.city_id = sci.city_id LEFT JOIN business_regions salary_business_regions ON jls.business_region_id = salary_business_regions.business_region_id LEFT JOIN business_regions applicant_locations_business_regions ON jal.business_region_id = applicant_locations_business_regions.business_region_id;
COMMIT;