System Information
- Strapi Version: 4.1.7
- Operating System: MacOS 12.3.1
- Database: MySQL 8
- Node Version: v16.13.0
- NPM Version: 8.5.1
What we need
We would like to execute “super.find(ctx)” to get the results filtered with the standard Strapi filters (passed via the GET request) and also filter results with custom MySQL query without breaking the pagination.
This table has more than 12.000 entries
What is the expected outcome
We want to filter trainers both with name, surname… and by distance to the client applying custom query.
What we are doing now
(Works but breaks the pagination because we are removing items from paginated results returned by super.find(ctx))
Follow comments in code. I tried to be clear as possible…
module.exports = createCoreController('api::trainer.trainer', ({ strapi }) => ({
async find(ctx) {
// STEP 1: calling super.find() to get paginated results applying standard Strapi filters (columns of the database)
const { data, meta } = await super.find(ctx);
// STEP 2: Retrieving latitude and longitude and other useful custom params
const latitude = ctx.request.query.latitude;
const longitude = ctx.request.query.longitude;
const distanceInKmMin = ctx.request.query.distanceInKmMin;
const distanceInKmMax = ctx.request.query.distanceInKmMax;
let newData = []; // support variable
/**
* STEP 3
* At this point, we are executing a raw query to get all trainers between the given
* distance range (we are calculating distance with latitude and longitude, but thats not important)
*/
if ((distanceInKmMin || distanceInKmMin === 0) && distanceInKmMax) {
const trainersDistances = await strapi.db.connection.raw(`SELECT * FROM (SELECT *, ROUND((ST_Distance_Sphere(point(trainers.longitude, trainers.latitude), point(${longitude}, ${latitude}))) / 1000) AS distanceInKm FROM trainers) a WHERE distanceInKm BETWEEN ${distanceInKmMin} AND ${distanceInKmMax} AND distanceInKm IS NOT NULL`);
/**
* THE PROBLEM: at this point we need to MIX the results from "super.find()" and the raw query to
* get only trainers filtered with strapi "super.find()" filters AND our custom logic.
* everything works fine but at this point we are
*/
for (let trainer of data) {
for (let trainerWithDistance of trainersDistances[0]) {
// Here we are simply creating a new array ("newData") where we push ONLY the trainers
// that are included in the distance range AND
if (trainer.id === trainerWithDistance.id) {
trainer.distanceInKm = trainerWithDistance.distanceInKm;
newData.push(trainer);
}
}
}
} else {
newData = data;
}
return { data: newData, meta };
},
}));