Hi guys,
I'm facing some optimizations problems through my laravel API. I work for a company which provide a suit of apps and we have in the main app some kind of feed with different kind of Threads.
To make quickly, this is a kind of formation companion app. So in the feed, a user have access to thread that are posted by other users, to content set up in the course, to other things also.
All of that is manage through a topics table, which can have attachment. The system actually have huge perf issue due to the manner its coded.
So far, I'm getting all the topics for the given course and then filter through them with permissions (through policy), then orders them with some rules (like last comment date) and them paginate them.
So for the app it looks paginated but in the api this is not, which involve very much perf issues. The pagination is really hard to set at the sql query level because, the permission check and order can make the last topics of the query first or second if there were activities on it.
So far, I have no idea how to optimize that, but still looking for ideas.
Let me know if this wasn't clear enough ^^
@davidsprauel Without knowing your database structure or model relationships, I’d look at starting to add joins to your main query to include authorisation checks. So if a user is only able to view a specific set of topics, then query that and create a whereIn clause from that set.
@martinbean The problem is that it touches at least 20 tables and this is complicated to show the structure without having a full explanation of the app.
But we have 9 plateform ( 9 differents app/website for each one is a role binded to it). Each plateform has his own authorizations rules.
Example: I'm the client and wanna check the feed of my participants, i can only see topics, posted through my company and my programs, but no other restrictions
Now I'm a student, my course is divided into differents stages. In each stages, so topics are auto created to share the content in the feed. I must see peers topics, content for stage that are already in progress. I must have access to the content by several rules (individual, team, and some more)
That's quickly summarized but I have honnestly at least 1000 line of check before returning the topic :/
Please or to participate in this conversation.