How to add distance filters and sorters to strapi postgresql databbase in a graphql endpoint?

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;
    `);
  }```

do you find any solution ?

Unfortunately no. We gave up on graphql here and created a REST endpoint and wrote all the SQL queries ourselves

you are right i solved it by customizing the endpiont