How should we design API which need to aggregate (join) from multiple database tables [closed]

We are working on product which provide API for listing, with filtering, sorting, paginating ... The response result is aggregating from multiple database tables, to enrich data from those tables, or for filtering, sorting.

I"m considering to 2 solutions

1. JOIN multiple tables, with condition for querying, and order for sorting.
2. Create a view (or temporarily table) for storing aggregated data, it's transform from all related tables, and then API will query on that table only. Each record on that table will be updated once business tables have changed.

For the 1st solution, I think it's easy for maintaining consistent data, however it's hard for scaling, and the performance could be impact a lot because of many join. Especially for query on particular fields.

On another hand, 2nd solution is suitable for CRUD behaviour, but we need to maintain a logic for updating data on temporarily table, also we might need to add more field to that table if we are asked to show more data in the API.

I don't know exactly which design is good, and do we have any design pattern suitable for this requirement