Open flsilva opened 9 months ago
What's the best way to implement this?
I understand OpenTask is very simple and doesn't need advanced or optimized solutions, but the goal of the project itself is to discuss and learn good ways to solve problems, not just solve the problems in any possible way.
Currently, we only have to query the Project
table to render <ProjectList>
:
https://github.com/flsilva/opentask/blob/main/src/features/app/projects/data-access/ProjectsDataAccess.ts#L101
Option A:
We could query the Task
table to fetch all projects' incomplete tasks.
Upside:
Simple to implement, and we don't need to worry about data mutations (creating or deleting tasks, completing tasks, reverting completed tasks to incomplete, etc) as we'd always fetch a fresh list of projects and tasks to render the number of incomplete tasks.
Downside:
Not optimized. We need to query the Task
table alongside the Project
one for every project.
Option B:
We keep an updated totalIncompleteTasks
column in the Project
table.
Upside:
Very optimized to query data. Even though we'd need to query the Task
table for different tasks' mutations, data mutation is less frequent than querying it.
Downside:
Complex to implement at the app level. We need to keep totalIncompleteTasks
in sync with data mutations, which means every time we create, delete, complete, or revert a completed task to incomplete, we have to update totalIncompleteTasks
, which requires more work/code, increasing the surface area for bugs.
Implementing it as a Postgres function/stored procedure would probably be simpler, but I avoid them as much as possible. I believe we must keep all business logic in the application and only resort to any other way, like SPs, when we absolutely have to.
Of the two options above, I prefer Option A. Do you know a simple alternative? Please share it with us.
In my opinion if we couldn't have have the value calculated in database as triggrer or procedure should be the best in terms of performance.
But my first approach would be "Option A" but not querying Task
for every Projects
. I'll do one query to the Task table grouping by Project and filtering with not completed. Something like this
SELECT projectId, count(*)
FROM Task
WHERE completedAt NOT NULL
GROUP BY projectId
Discussed in https://github.com/flsilva/opentask/discussions/8