Using the Tree View in Perspective

Important - am using MySQL 5.6

I am trying to make a perspective tree view component show a heirarchy of items. I am able to get the first level easy enough, but making the “items” property set to the children seems to be impossible in 5.6. Has anyone done this? I read that I might have to update to MySQL 5.7 for this capability to do stuff with JSON, is that true or is there a way to work around it?

I tried

SELECT id as 'data', name as 'label'
FROM customers

and that does populate my highest level fine.

But my attempts at doing the customers children whether as a subquery or a join either 1) don’t work at all because of SQL rules or 2) puts the children on the same level as the customers.

What you’re likely thinking of is the native ability for MySQL 5.7+ to store JSON objects, which doesn’t appear to be necessary in your case (unless you wanted to build out all your tree views in JSON and then save them in your database, which seems redundant with your existing tables and would be a big pain if you happen to have a lot of them).

Instead, you should be able to utilize scripting to take data from your data and format it in the way the tree view expects. If you’re populating that Tree View from a query binding, you can add a script transform onto that binding. Within that script, you’ll probably have to call additional queries to grab the data for the child items (from the sound of your database structures). At the end of the script, you can simply return a python object, which should be a list containing dictionaries which match the fields of a Tree View’s props.items.

Hope this sets you in the right direction.