How to calculate the percentage of a nested table architecture in mysql

My current tables architecture is the following:

1. Card Table ( Parent table: Card)
2. Each card has one or many lists (Second child lists)
3. Each list has many tasks (third child tasks)
4. Whenever the user fill a task it will be filled in a separate table called (user_tasks)

So if you want to imagine it it’s like a Tree Card => many cards lists => many lists tasks.

Card

ID | Name

Lists

ID | Card_ID | Name

Tasks

ID | Lists_Id | Card_Id | Title

User Tasks

Id | Task_id | User_Id | Content

Now I need to write a query or a simple logic whatever it is that I can’t figure out at all to calculate the progress of each card.

the card progress is calculated by how Lists the user completely filled.

Examples:

1. Card 1 has 3 lists each one has 4 tasks

User filled all tasks (4×3) in these lists the card progress will be 3/3.

2. Card 1 has 3 lists each one has 4 tasks

User filled all tasks in any 2 lists (4×2) the card progress will be 2/3

3. Card 1 has 3 lists each one has 4 tasks

User filled only 2 tasks in list 1 that contains 4 tasks the card progress will be 0/3

So to consider a list done of fulfilled the user has to fill all its tasks. can someone guide me on how to do the logic of a nested tables architecture to get the cards to progress, please?

So I expect the output to be as the following in any way:

{
   card_id: 1,
   total_lists: 10
   total_filled: 3
}

and goes on, on all cards in the databse…

Answer

You can use the below query

SELECT target.cardid as 'card_id', target.total as 'total_lists', IFNULL(progress.total,0) as 'total_filled'
FROM   (SELECT l.cardid, Count(l.id) total 
        FROM   lists l 
        GROUP  BY l.cardid) target
LEFT JOIN (SELECT l.cardid, userlist.userid, Count(userlist.listid) total 
                   FROM   lists l 
                          INNER JOIN (SELECT ut.userid, t1.listid, Count(ut.taskid) tcount 
                                      FROM   user_tasks ut 
                                             INNER JOIN tasks t1 ON t1.id = ut.taskid 
                                      GROUP  BY ut.userid, t1.listid) userlist --- STEP#1
                                  ON l.id = userlist.listid 
                          INNER JOIN (SELECT t.listid, Count(*) tcount 
                                      FROM   tasks t 
                                      GROUP  BY t.listid) tasklist --- STEP#2
                                  ON userlist.listid = tasklist.listid  AND userlist.tcount = tasklist.tcount --- STEP#3
                   GROUP  BY l.cardid, userlist.userid) progress  -- STEP#4
ON target.cardid = progress.cardid; 

Description:

• Step#1: Determine the count of tasks per user_id and list_id
• Step#2: Determine the count of tasks per list_id for all lists
• Step#3: Filter records in user_task that does not have entries for all tasks in a list
• Step#4: Get list count in user_task that has entries for all tasks
• Finally, join the target and progress subqueries to fetch the final result.

Demo: https://www.db-fiddle.com/f/5tAzoL49SSd2SJDK5k19a3/3

---

Note: Couple of observations

1. Ideally, table tasks should not have cardid, only listid must be present.
2. Can 2 different users work on the same card? In that case, in the result, you need to include the userid as well. Refer – https://www.db-fiddle.com/f/5tAzoL49SSd2SJDK5k19a3/4