I am not much experienced with Strapi. I apologise if I am sound too novice. I am trying to create a custom route for fetching some complex data using two relations.
I've two relations: items and comments.
What I want to do is:
Select top 5 items with most comments from the comments table using GROUP BY method (maybe).
Then use the object_id field from this items, and get the data from items table for that id (column saved by the name id in this table)
So basically, I want to fetch the items which has the most comments. But the data for the items is in the items table, and the data needed for the getting the id of the most commented items is in the comments table.
I've already created a new route and a constructor for the same. I am able to call that route and it also triggers the constructor. But I am not sure which operations to run to get the data that I need. I am using MySQL.
I am confused that should I handle this the way I'm thinking and try to create a custom API in Strapi itself which Frontend can directly call to get the required data, or should I make multiple API calls in the Frontend itself and handle it all in the frontend. I know how to create a query for two tables to produce the expected output. But I am not sure where and how should I use the query in Strapi to create a custom API route
Thank you very much for the help.
Strapi uses Knex.js as its internal query builder. In this case, you can do any MySQL operations in the JavaScript manner through Knex.js functionalities.
You can find out how to use Knex.js in this documentation.
To use Knex.js in Strapi you can do something like this inside a controller/service.
const knex = strapi.strapi.db.connection.raw;
const data = await knex.select('*').from('data_table_name'); // or any other query operations you want to do
You can also refer to answers from this question to learn more about Knex.js connection in Strapi.
Hope that helps!