1

I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom.

A "person" can have many "jobs" and each of those "jobs" have many "focuses". The focuses tell us information about these people like what sector or geo focus they specialize in.

I'd like to be able to run 2 queries against the set to rank/filter people by their job focuses. One query would be inclusive of everyone (regardless of the focuses I'm searching for) and the other one would exclusively show people that match what I'm passing in. In both cases I'd want them ranked in order of the most overlap with the input data I'm passing in.

An example query I'd like to run (in words) would be

"Find people with a geo focus of 'CAD' that have a sector focus of 'tech' or 'finance'".

The results for an exclusive query would ideally look like this, ordered by the length of matching focus values

| person_id | first_name  | last_name | focus_values  |
-------------------------------------------------------
| 2         | jen         | smyth     | {tech,finance}
| 3         | jon         | smath     | {finance}

And an inclusive query would have everyone else below with an empty {} of focuses

For reference, the API endpoint will receive something like this hash as the filtering params:

{market_cap: ['small', 'mid'], sector: ['mining']}

Where the keys of the hash are the 'category' and the values are... the 'value'.

In the inclusive case I just want everyone returned, but ranked by the number of focuses values that overlap with the input data

In the exclusive case I only want people that have at least one of the values from each category passed in.

I feel like this involves a windowing query of some sort but I can't quite wrap my head around it.

So far all I've figured out is ordering people by the number of focuses they have, but I can't seem to figure out the intersection of the focuses vs the input ones we care about in order to filter people out and reduce the focuses down to only the ones I care about.

Here's that query. Obviously its pretty simple but completely ignores the actual input data itself. Since it's inner joining this would be an exclusive match (if I could figure out the intersection part). I imagine an inclusive search could then just join again against contacts. Not sure if there's a more efficient way that works for both though.

SELECT
  "people"."id",
  ARRAY_REMOVE(ARRAY_AGG(DISTINCT category_value), NULL) AS focuses
FROM "people"
INNER JOIN "jobs"
  ON "people"."id" = "jobs"."contact_id"
INNER JOIN "focuses"
  ON "jobs"."id" = "focuses"."retail_job_id"
GROUP BY "people"."id"
ORDER BY ARRAY_LENGTH(ARRAY_REMOVE(ARRAY_AGG(DISTINCT category_value), NULL), 1) DESC NULLS LAST
;

Data model:

People
| person_id | first_name  | last_name |
---------------------------------------
| 1         | abe         | smith     |
| 2         | jen         | smyth     |
| 3         | jon         | smath     |
| 4         | bob         | smoth     |
| 5         | sue         | smuth     |

Jobs (fk: person_id => People)
| job_id    | person_id   | title     |
---------------------------------------
| 11        | 1           | CEO       |
| 22        | 2           | CTO       |
| 33        | 3           | CPO       |
| 44        | 4           | CFO       |
| 55        | 5           | COO       |

Focuses (fk job_id => Jobs)
| job_id    | category    | value     |
---------------------------------------
| 11        | sector      | mining    |
| 11        | sector      | energy    |
| 11        | market_cap  | small     |
| 11        | market_cap  | mid       |
| 11        | geo         | EUR       |
| 11        | geo         | CAD       |
| 22        | sector      | tech      |
| 22        | sector      | finance   |
| 22        | sector      | utilities |
| 22        | geo         | CAN       |
| 33        | sector      | mining    |
| 33        | sector      | utilities |
| 33        | sector      | finance   |
| 33        | geo         | USA       |
| 33        | geo         | CAN       |
| 33        | market_cap  | large     |
| 44        | sector      | mining    |
| 44        | market_cap  | mid       |
| 44        | market_cap  | large     |
| 55        | sector      | mining    |
| 55        | geo         | CAN       |
| 55        | market_cap  | small     |
| 55        | market_cap  | mid       |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.