Issue with implementing logic to filter top 10 data-CodePudding

I am trying to list similar jobs based on the current job the user is viewing.

Current Job Location:

Local Zone: Vancouver Downtown

City: Vancouver

State: British Columbia

Now, I want to show similar jobs based on the following criteria :

Step 1: Filter jobs based on "Vancouver Downtown", "Vancouver" and "British Columbia" and return the 10 jobs order by ""Vancouver Downtown" localzone

Step 2: If we don't find 10 jobs in step 1 then we find jobs in "Vancouver" and "British Columbia" and return the 10 jobs order by "Vancouver".

Step 3: If we don't find 10 jobs in step 2 then we find jobs in "British Columbia" and return the top 10 jobs order by just "British Columbia".

So search order to find similar jobs starts from lowest level that is "Local Zone", then if not there, we move to "City" and vice versa.

In the final result, I want to list down the jobs starting from the lowest level "Local Zone" and then we move to "City" and then "State".

Table :

Jobs:
JobId   Title      LocationId       Salary


Location:
LocationId    CountryId      StateId    CityId      LocalZoneId

Query :

DECLARE 
    @currentJobId INT = 101,
    @currentJobLocalZoneId INT = 201,
    @currentJobCityId INT = 301,
    @currentStateId INT = 401
SELECT TOP 10
        j.JobId,
        j.Title,
        j.Salary
        l.StateId,
        l.CityId, 
        l.LocalZoneId
        FROM Jobs j
        LEFT JOIN Location l ON l.LocationId = j.LocationId
    
        WHERE 
            j.JobId != @currentJobId
            AND (@currentJobLocalZoneId IS NULL OR l.LocalZoneId = @currentJobLocalZoneId)
            AND (@currentJobCityId IS NULL OR l.CityId = @currentJobCityId)
            AND (@currentStateId IS NULL OR l.StateId = @currentStateId)
 
        ORDER BY 
            -- not sure how do i order results that starts from lowest level "Local Zone" and then we move to "City" and then "State"

But here in above query, I am confused with how do I implement bottom up approach of first starting from "Localzone" and if we don't find 10 jobs then we move to "City" and then "State".

Can someone please help or guide me to implement this part?

CodePudding user response：

The idea is to create an expression that returns an integer representing the "closeness" of the result. For example, in your case, you could use:

CASE WHEN l.LocalZoneId = @currentJobLocalZoneId THEN 1
     WHEN l.CityId = @currentJobCityId THEN 2
     WHEN l.StateId = @currentStateId THEN 3
     ELSE 4
END

...and then you ORDER BY this expression.

CodePudding user response：

Assuming that the parameters are part of a hierarchy, i.e. the local zone is contained in the city which is contained in the state, you need to perform a query that combines the results of increasingly broad matches:

select top 10 JobId, Title, Salary, StateId, CityId, LocalZoneId
  from (
    -- Local Zone.
    select J.JobId, J.Title, J.Salary, L.StateId, L.CityId, L.LocalZoneId,
      1 as OrderRank -- These results come first.
      from Jobs as J left outer join
        Location as L on L.LocationId = J.LocationId
      where J.JobId != @currentJobId and -- Never include the current job.
        L.LocalZoneId = @currentJobLocalZoneId
    union all
    -- City.
    select J.JobId, J.Title, J.Salary, L.StateId, L.CityId, L.LocalZoneId,
      2 -- Second choice results.
      from Jobs as J left outer join
        Location as L on L.LocationId = J.LocationId
      where J.JobId != @currentJobId and -- Never include the current job.
        L.CityId = @currentJobCityId
    union all
    -- State.
    select J.JobId, J.Title, J.Salary, L.StateId, L.CityId, L.LocalZoneId,
      3 -- Third choice results.
      from Jobs as J left outer join
        Location as L on L.LocationId = J.LocationId
      where J.JobId != @currentJobId and -- Never include the current job.
        L.StateId = @currentStateId
    ) as FilteredJobs
    order by OrderRank;

Note that the filter values need to be set properly, e.g. if you want to filter by City then both @currentJobCityId and @currentStateId need to be set correctly to allow the fallback from City to State if the City doesn't have 10 appropriate jobs available.