All we need is an easy explanation of the problem, so here it is.
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
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
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
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?
pillars column is indexed with a gin index btw
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
That sounds simple:
ORDER BY array_position(pillars, 'c'), st_distance(....)
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂