Writing some services on a system I'm working on has raised some questions as to how to provide the data formatted for the client (views or mobile app). Basically, my doubt is born whenever I need to provide grouped data or data that needs to be changed after the query.
I will use as an example a Has Many Through relationship:
countries id - integer name - string users id - integer country_id - integer name - string posts id - integer user_id - integer title - string
I'm using the basic example that the Laravel framework uses. But my structure is a bit more complex. Anyway, I'll try to put together a scenario based on this structure.
Let's assume that, by business rule, whenever I need to list posts, they will need to be grouped by countries. Therefore, I believe that because of being a business rule requirement, whenever my clients request posts, the posts service must provide the grouped data.
In the case above it is very simple, in the real case I have to additionally group, change some information of the fields of each country record and their respective posts. And at this point, I always wonder about the 'best' way to accomplish this data formatting.
- I have worked on systems that the fields that needed to be changed, were already solved through a view in the database that made the necessary relationships and already provided everything ready. Letting the programming solve the data grouping and possible changes of structures.
- In other systems, we used a powerful ORM that could do great inner joins while maintaining performance (which is not the case for all ORMs).
- And in other cases, due to the ORM-generated slowness of the framework we used, we made several small queries and then grouped and altered the data programmatically in repetitive structures.
First question: is there a RIGHT way to do this kind of work?
Second question: If it is a CASE question (which I believe it to be), if in any project I create several different ways to group data, will I be creating bad code?
Third question: if by chance I decide to do the data groupings programmatically, make a query in the database and then change the state of that result in the code, would it be a bad practice?
Sometimes I think that views in the database are always the best solutions. But in my real case, there are almost 120 tables. And I already have a lot of code that deals with large relationship structures programmatically from those tables. I think starting to write views now would create a mess from where the business rule is located.
What do you think about all these cases?