I'm not quite sure what the best way to phrase this is...
So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.
I'm using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.
so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c
the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3
I'm really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0
Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?
The pillars column is indexed with a gin index btw
