System Information
- Strapi Version: 4.2.2
- Operating System: Linux
- Database: Postgresql
- Node Version: 16.17.0
- NPM Version: 7.5.2
- Yarn Version: 1.22.19
Hi
In one of our projects we have a Strapi backend with a Postgresql db attached to it and we are using graphql for requests. In the database there is a table of locations with their coordinates. We were trying to add sorters and filters to the graphql requests that would allow us to sort the data by distance from given location and filter them by given maximum distance from a given location. We know how to extend and modify graphql endpoint on backend using nexus and we have a sql function for calculating the distance on the database side but we are couldn’t find a way to use this function in those graphql endpoints.
Is it possible to do that?
async function createDistanceFunction() {
await strapi.connections.default.raw(`
CREATE OR REPLACE FUNCTION calculate_distance(lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS float AS $dist$
DECLARE
dist float = 0;
radlat1 float;
radlat2 float;
theta float;
radtheta float;
BEGIN
IF lat1 = lat2 OR lon1 = lon2
THEN RETURN dist;
ELSE
radlat1 = pi() * lat1 / 180;
radlat2 = pi() * lat2 / 180;
theta = lon1 - lon2;
radtheta = pi() * theta / 180;
dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);
IF dist > 1 THEN dist = 1; END IF;
dist = acos(dist);
dist = dist * 180 / pi();
dist = dist * 60 * 1.1515;
dist = dist * 1.609344 * 1000;
RETURN dist;
END IF;
END;
$dist$ LANGUAGE plpgsql;
`);
}```