Skip to content
Advertisement

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.

  1. 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

  1. 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:

and goes on, on all cards in the databse…

Advertisement

Answer

You can use the below query

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement