I am relatively new to Strapi, so please forgive my lack of experience. My goal is to set up a custom route for retrieving complex data using two related tables.
I have relationships with the "items" and "comments" tables.
My approach involves selecting the top 5 items with the most comments from the "comments" table using the "GROUP BY" method. I then plan to use the "object_id" field from these items to retrieve corresponding data from the "items" table based on the ID stored in the "id" column.
In essence, I want to fetch items with the highest number of comments. However, the required data for this task resides in two separate tables: "items" and "comments."
I have already established a new route and constructor for this purpose. While I can successfully call the route and trigger the constructor, I am unsure about the specific operations needed to collect the necessary data. My database management system is MySQL.
I am contemplating whether to create a custom API within Strapi for direct frontend access or to make multiple API calls from the frontend itself. Although I understand how to write queries across multiple tables to obtain the desired results, I need guidance on incorporating the query into Strapi for creating a custom API route.
Your assistance is greatly appreciated. Thank you!