System Information
- Strapi Version: v4.21.1
- Operating System: mac os
- Database: sqlite
- Node Version: 20.x
- NPM Version:
- Yarn Version: 1.22
My goal is to make a general function named listgroup that I can call like this: http://localhost:1337/api/stat/listgroup/person?countByField=businessOwnerApps
The function should do the following:
On the table person. Find the persons that have the most relations in the field businessOwnerApps and return the top 10 persons.
Strapi does not have group by functionality, so I use knex to do the query. It works just fine. BUT it requires that I know the name of the table that holds the relation.
The nice thing about Strapi is that they have hidden these details for you. It makes it easy to do queries that filter and sort. But when you need to do reporting, it becomes a problem.
Strapi obviously knows the names of the tables that hold the relationships. My question is: how can I query Strapi and get the name of the table that holds the relation?
The system is a simple CMDB. It stores the relationship between employees and the applications and integrations. A person can have many roles. She can be the IT owner of an app, a business owner for another, and a stakeholder.
The code for the listgroup is below (including all my debugging):
// /backend/src/api/stat/controllers/stat.ts
import { snakeCase } from 'lodash';
import { Context } from 'koa';
import { Strapi } from '@strapi/strapi';
import qs from 'qs';
const contentTypes: Record<string, string> = {
app: 'api::app.app',
'app-relation': 'api::app-relation.app-relation',
integration: 'api::integration.integration',
person: 'api::person.person',
// Add other mappings as needed
};
/**
* guessStrapiRelationTableName - an ateempt to guess the name of the table that holds the relation between two tables in Strapi
* But this only works when i have used names like "businessOwnerApps" "itOwnerApps" on the relations.
* So it will not work for the relation named "stakeholderApps"
* @param relationName
* @returns
*/
function guessStrapiRelationTableName(relationName: string): string {
console.log('relationName INPUT:', relationName);
let guessedTableName = "";
// first convert to snake_case example "businessOwnerApps"
relationName = snakeCase(relationName); // it is now "business_owner_apps"
console.log('relationName:', relationName);
// then we find the last "_" and take the part after it as that should be in the start of the table name
const lastUnderscore = relationName.lastIndexOf('_');
if (lastUnderscore > -1) {
guessedTableName = relationName.substring(lastUnderscore + 1) + "_"; // we add "apps" and "_" to the table name
console.log('guessedTableName A:', guessedTableName);
// then we take the part before the last "_" and add it to the table name
guessedTableName += relationName.substring(0, lastUnderscore); // we add "business_owner" to the table name. we now have "apps_business_owner_"
console.log('guessedTableName B:', guessedTableName);
// then we add "links" to the table name
guessedTableName += "_links"; // we add "_links" to the table name. we now have "apps_business_owner_links"
console.log('guessedTableName C:', guessedTableName);
} else {
guessedTableName = relationName + "_links"; // if there is no "_" in the name we just add "_links" to the name
}
return guessedTableName;
}
export default ({ strapi }: { strapi: Strapi }) => ({
/**
* listgroup - returns the n records in a collection that :
* a collection has relations to another collection.
* This function counts the number of relations a collection have for the relation specified by the countByField.
* It then returns the n records with the highest count. Sorted so that the record with the highest count is first.
* countByField is the field name of the relation
*
* Other query parameters are used as filters
* Call it using: http://localhost:1337/api/stat/listgroup/person?countByField=businessOwnerApps&email=my@email.com
* @returns
{
"status":"success",
"message":"Filter: none Counted by businessOwnerApps",
"data": [
{
"total": 49,
"name": "Error",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
{
"total": 29,
"name": "Getting",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
{
"total": 19,
"name": "Data",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
]
}
*/
async listgroup(ctx: Context) {
const dummyResponse = {
"status": "success",
"message": "Filter: none Counted by businessOwnerApps",
"data": [
{
"total": 49,
"name": "Error",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
{
"total": 29,
"name": "Getting",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
{
"total": 19,
"name": "Data",
"idField": "some.id.field",
"subField": "some.extra.text",
"imageField": ""
},
]
};
try {
const { collectionName } = ctx.params;
const queryParams = qs.parse(ctx.querystring);
const contentType = contentTypes[collectionName];
if (!contentType) {
ctx.status = 400; // Bad Request
ctx.body = {
status: "error",
message: "Invalid collection name",
errors: [`Collection ${collectionName} is not recognized.`],
data: dummyResponse
};
return;
}
const model = strapi.getModel(contentType as any);
if (!model) {
ctx.status = 400; // Bad Request
ctx.body = {
status: "error",
message: "Invalid collection name",
errors: [`Model for collection ${collectionName} could not be found.`],
data: dummyResponse
};
return;
}
// now lets deal with the parameters.
// Extracting the countByField and removing it from queryParams
const countByField = queryParams.countByField;
delete queryParams.countByField; // Remove countByField from queryParams to apply the rest as filters
const nameField = queryParams.nameField || 'name';
delete queryParams.nameField;
const idField = queryParams.idField || 'id';
delete queryParams.idField;
const subField = queryParams.subField || 'subField';
delete queryParams.subField;
const imageField = queryParams.imageField || 'logo';
delete queryParams.imageField;
const maxItems = queryParams.maxItems || 10;
delete queryParams.maxItems;
// there is a chance that parameters passed does not exist on the model. This will cause an error.
// so we need to check if the parameters passed are valid.
const validFields = Object.keys(model.attributes);
const invalidFields = Object.keys(queryParams).filter(field => !validFields.includes(field));
// now check if he special fields are valid
if (!validFields.includes(countByField)) {
invalidFields.push(countByField);
}
if (!validFields.includes(nameField)) {
invalidFields.push(nameField);
}
if (!validFields.includes(idField)) {
invalidFields.push(idField);
}
if (!validFields.includes(subField)) {
invalidFields.push(subField);
}
if (!validFields.includes(imageField)) {
invalidFields.push(imageField);
}
if (invalidFields.length > 0) {
ctx.status = 400; // Bad Request
ctx.body = {
status: "error",
message: "Invalid filter field(s):" + invalidFields,
errors: invalidFields,
data: dummyResponse
};
return;
}
// Accessing the Knex instance for direct database operations
const knex = strapi.db.connection;
// now we need to find the table name for the relation
const relationTableName = guessStrapiRelationTableName(countByField);
console.log('relationTableName:', relationTableName);
const theBase = collectionName + "s" + " as base"; // the "s" must be there
console.log('theBase:', theBase);
const theRelated = relationTableName + " as related";
console.log('theRelated:', theRelated);
const theRelatedID = "related." + collectionName + "_id";
console.log('theRelatedID:', theRelatedID);
let query = knex(theBase)
.leftJoin(theRelated, 'base.id', '=', theRelatedID)
.groupBy('base.id')
.orderByRaw('COUNT(related.id) DESC')
.limit(maxItems)
.select(['base.id'])
.count('related.id as totalLinks');
// Apply filters from queryParams
Object.keys(queryParams).forEach(key => {
// Assuming queryParams are already in the correct format (snake_case) for your database
query = query.where(`base.${key}`, queryParams[key]);
});
const idsWithCount = await query;
// Extract IDs for the findMany operation
const ids = idsWithCount.map(item => item.id);
const strapiQuery = {
filters: { id: { $in: ids } },
fields: [nameField, subField, idField], // select the fields we got via the parameters
populate: {
[imageField]: // imageField
{
fields: ['url'],
},
}
};
// Use Strapi's entity service to fetch detailed entries based on the obtained IDs
const entriesWithDetails = await strapi.entityService.findMany(contentType as any,
strapiQuery
);
//console.log('entriesWithDetails:', JSON.stringify(entriesWithDetails, null, 2));
// Map the results to the expected format
const results = entriesWithDetails.map(entry => ({
total: idsWithCount.find(item => item.id === entry.id).totalLinks,
name: entry[nameField],
idField: entry[idField],
subField: entry[subField],
imageField: entry?.[imageField]?.url || ""
}));
// sort so that the record with the highest count is first
const sortedResults = results.sort((a, b) => b.total - a.total);
//console.log('sortedResults:', JSON.stringify(sortedResults, null, 2));
// convert queryParamsString to a string like this: pagaremter=value, parameter2=value2 if there are any. if not it will be set no "none"
const queryParamsString = Object.keys(queryParams).map(key => `${key}=${queryParams[key]}`).join(', ') || 'none';
ctx.body = {
status: 'success',
message: `Filter: ${queryParamsString} Counted by ${countByField}`,
data: sortedResults
};
} catch (error) {
ctx.status = 500; // 'Internal server error');
ctx.body = {
status: "error",
message: "Try/catch error:" + error.message,
errors: error,
data: dummyResponse
};
return;
//console.error('Error in countgroup:', error);
//ctx.throw(500, 'Internal server error');
}
},
});