Level 122
Unless the data is very large, load it all in the browser to start with, and toggle the visibility on and off.
Use https://github.com/lazychaser/laravel-nestedset for the back-end
1 like
Sep 15, 2022
19
Level 8
Dynamically adding rows to a table with hierarchy
I have table with hierarchy and nested rows. So at first I only display the highest hierarchy in 1 row. Then on click of it, it should open next level, and so on. (Need to be able to fold it back too)
But the initial load of the data of each next hierarchy is done with AJAX so the rows are dynamically added.
This is a basic structure of what I mean:
Main Row
|______SubData1
| |______SubSubData1
| |______SubSubData2
|______SubData2
| |______SubSubData3
| |______SubSubData4
| |______SubSubData5
| |______SubSubData6
|______SubData3
| |______SubSubData7
|______SubData4
|______SubSubData8
|______SubSubData9
|______SubSubData10
It's a nested tree but the actual table will not be nested (When you open up the next hierarchy it should just open up rows below the clicked parent)
How should I go about doing that if the number of nested elements is not known?
I was thinking about assigning data attributes to the rows based on the table row id in the DB, then add listeners using jQuery for click events. Then whenever a user clicks on this row (maybe put a little "expand" arrow), it will send an AJAX request to the next level in the hierarchy, then append the data as new table rows using after the specific clicked parent.
Example: I click on the Main Row - it sends AJAX and gets all SubDatas from 1 to 4, and appends them below it.
Then if I click of SubData2, it will send another AJAX and append the four SubSubDatas between the row of SubData2 and SubData3
Is it OK? Is there anything I need to think about before starting? Maybe because the table loads dynamically then something about the CSS? Because I know there are some troubles with tables and CSS when the size is not known
Thanks!
Level 73
I agree with @snapey load everything and toggle visibility on and off. Sending an ajax request every time someone clicks on a header or sub-header would probably affect the user experience.
1 like
Level 8
@tray2 @snapey currently it's done like that: Everything is loaded together. There are plenty of rows. Now each of this sub-hierarchy is a sum of 50+ columns for each hierarchy.
Imagine it's an hierarchy of some company so it goes all the way from large units to individual workers.
So let's say when you first load the page, it shows sum of all 50+ columns (it's money-related columns) of all the hierarchies below, then you click this first line, it opens the next sub-hierarchy which is the separate divisions of the company. So it's sum of each division individually. Then you click any of the division, it opens like dozen other sub-units in this division. Then if you click this sub-unit, it opens all the workers in this sub-unit. So the lowest level of this hierarchy is the worker-level which also is a sum of money-related stuff per worker (There is more than one row per worker)
So currently what's going on is that there is a loop of DB queries per each individual worker, per each sub-unit.. and going up till you reach the highest level. Something ridiculous which doesn't work properly and makes the page loads in more than 1 minute.
I am not sure if it's because something is wrong with the loops, or that's just what you'd expect when you do loops of DB queries that SUM things (many GROUP BYs as you can imagine, per each hierarchy)
You think that I can still change the table in this situation I described above to load everything first and still be fast enough? Or I should then do AJAX?
I was also thinking to at least load always the next sub-hierarchy: So at first the user see the highest level, but behind the scenes we're already loading the next sub-levels.. but :/ not sure
Level 73
@Ligonsker Install the Laravel debugbar and give it a spinn, if it is to slow, try optimizing your queries, if it's still too slow and the queries are fast then try using ajax.
1 like
Level 8
@Tray2 thanks. Btw, after you see the needed data to be displayed (those nested hierarchies), do these loops of DB queries look OK? Or it's still weird to see DB queries inside loops?
Level 73
@Ligonsker Yeah, it's a bit wierd, if you had been using an Oracle database you could have used CONNECT BY PRIOR which is designed for these kind of things. However, doing these things in MySQL is a bit trickier.
There is something called recursive queries in MySQL 8, that might help you make it better.
Check this thread for some solutions
https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query
1 like
Level 8
@Tray2 It's MS DB 😅
Level 73
@Ligonsker I'm so sorry to hear that, but MS SQL has something similar to Oracle
https://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server
1 like
Level 8
@Tray2 Instead of that, do you remember you told me about views? Right now, the lowest level hierarchy is the sum of each worker columns under specific sub-sub hierarchy.
So let's say worker X has 50 rows under Unit->SubUnit->SubSubUnit->SumOfWorker_X
Will it be a good idea to maybe create a view that holds the sum of each worker grouped by (unit, subunit, subsubunit, worker), and then instead of fetching all the data, I will have a view that already has that information.
And I will have to fetch all the individual rows from that view, already calculated per lowest unit. Then each level up in each hierarchy will be sum of all these workers. But then it's much quicker calculation, can be done with PHP too?
**Of course worker X can have many more rows in another sub sub hierearchy: Unit2->SubUnit2->SubSubUnit5->SumOfWorker_X
So Imagine that right now, for each of these lowest hierarchy levels, there is a DB query, thousands in foreach loop, of course it makes no sense
Level 73
@Ligonsker The view doesn't hold any data, it is a query inside the database that you query from your application. So every time you query the view the query in the view is run. There are of course some other benefits of creating a view though.
There are however another way to do what you thought a view was, it's called a materialized view, what that does, is that it creates a kind of temporary table by copying the result from the query into said table, and if the data doesn't change that often you can run a refresh during the night or on set timer.
1 like
Level 8
@Tray2 oh. But temporary won't be good as it changes many times. Perhaps there should be a new table that sums the individual workers grouped by lowest hierarchy especially for this page? Or it's not a good convention to make a special table for that?
Level 73
@Ligonsker It isn't really temporary, it is more like a table that contains copied data from a query. It's not a real table in that sense, but it acts like a table and a view at the same time, but has like I said a copy of the result of the query it is built upon.
1 like
Level 8
@Tray2 so I set a query which will group by Sum of the lowest hierarchy and that's it, it's ready to be queried in that front end table I'm working on? Won't that be the best solution in my case instead of looping these sums whenever a user loads the page?
Does it have any downside?
Also, instead of updating it once a night, it's it possible to trigger it on every change of the original table if real time data is important? It doesn't update that often but still need to be real time data.
Update: this is the quote from the link:
A Materialized View persists the data returned from the view definition query and automatically gets updated as data changes in the underlying tables.
So it does actually updates the Materialized View as soon as the other table updates?
Level 73
@Ligonsker Depends on how you have defined the materialized view.
As for your first question, I don't know, give it a try and see what happens. :)
1 like
Level 8
@Tray2 thank you
There's one more option I was thinking about- to create another table to hold the grouped data, and create a stored procedure that would update it every time the original table updates. Could that be a good option as well?
Level 73
@Ligonsker That would work yes, but then you have more than one single point of truth.
1 like
Level 8
@Tray2 Oh yes. I just read about indexed views / materialized views are hard to create and can cause more trouble than what they are trying to solve and also need too many constraints. Also the docs regarding how to create it are pretty vague and no tutorials on how to actually create that. Not sure I will be able to make that work.
Have you ever dealt with 2 sources of truth? Or it should be avoided?
Level 73
@Ligonsker Not that many times, the database model I work with is very good, but there are some duplicate sources. If it can be avoided to store the same data in more than one place it should, but like everything, it sometimes unavoidable.
I agree that the docs and examples for materialized view is most of the time lacking.
I have no experience what-so-ever with MS SQL Server, so I can't help you there.
1 like
Level 8
@Tray2 Thank you for the ideas you gave me, going to see which will be the best
Please or to participate in this conversation.