How to populate a Javascript Dynamic Tree with data from an SQL query

Question

I have scoured the internet and this website for an answer to this question and I have yet to see it. My apologies if I missed a post.

What I am trying to do is populate a tree in Javascript using data that is returned from an SQL query.

The SQL query will return the following data...

ROOT_NODE_ID LEVEL1_ID LEVEL1_NAME LEVEL2_ID LEVEL2_NAME LEVEL3_ID LEVEL3_NAME LEVEL4_ID LEVEL4_NAME LEVEL5_ID LEVEL5_NAME LEVEL6_ID LEVEL6_NAME LEVEL7_ID LEVEL7_NAME LEVEL8_ID LEVEL8_NAME LEVEL9_ID LEVEL9_NAME LEVEL10_ID LEVEL10_NAME

Example...

Root  (ROOT_NODE_ID)
   Company 1  (LEVEL1_ID)
       Location 1  (LEVEL2_ID)
           Information1  (LEVEL3_ID)
       Location 2  (LEVEL2_ID)
   Company 2  (LEVEL1_ID)

-ROOT_NODE_ID (ROOT) points to the parent of the current node.

-LEVEL1_ID (Company 1) points to the Child of the root

-LEVEL2_ID (Location 1) points to the Child of LEVEL1_ID

-LEVEL3_ID (Information 1) points to the Child of LEVEL2_ID

-Company 2 would be created the same way with another row of data from the SQL query

-Location 2's ROOT_NODE_ID would equal Company 1, because Company 1 is the parent of Location 2

I hope this makes sense. I am currently using jquery and this is how I'm building my tree...

$(function(){

$("#tree2").dynatree({
    checkbox: true,
    }
});

var rootNode = $("#tree2").dynatree("getRoot");
// Call the DynaTreeNode.addChild() member function and pass options for the new node

//Adding Root
var Root = rootNode.addChild({
    title: "Root",
});

//Adding Level 1
var Company1 = Root.addChild({
    title: "Company 1",
});

//Adding Level 1
var Company2 = Root.addChild({
    title: "Company 2",
});

//Adding level 2
var Location1 = Company1.addChild({
    title: "Location 1",
});
//Adding level 2
var Location2 = Company1.addChild({
    title: "Location 2",
});

//Adding level 3
var Information1 = Location1.addChild({
    title: "Information 1",
});

});

mh

<metadata>
<item name="ROOT_NODE_ID" type="xs:decimal" precision="38" /> 
<item name="LEVEL1_ID" type="xs:string" length="2002" /> 
<item name="LEVEL1_NAME" type="xs:string" length="512" /> 
<item name="LEVEL2_ID" type="xs:string" length="2002" /> 
<item name="LEVEL2_NAME" type="xs:string" length="512" /> 
<item name="LEVEL3_ID" type="xs:string" length="2002" /> 
<item name="LEVEL3_NAME" type="xs:string" length="512" /> 
<item name="LEVEL4_ID" type="xs:string" length="2002" /> 
<item name="LEVEL4_NAME" type="xs:string" length="512" /> 
<item name="LEVEL5_ID" type="xs:string" length="2002" /> 
<item name="LEVEL5_NAME" type="xs:string" length="512" /> 
<item name="LEVEL6_ID" type="xs:string" length="2002" /> 
<item name="LEVEL6_NAME" type="xs:string" length="512" /> 
<item name="LEVEL7_ID" type="xs:string" length="2002" /> 
<item name="LEVEL7_NAME" type="xs:string" length="512" /> 
<item name="LEVEL8_ID" type="xs:string" length="2002" /> 
<item name="LEVEL8_NAME" type="xs:string" length="512" /> 
<item name="LEVEL9_ID" type="xs:string" length="2002" /> 
<item name="LEVEL9_NAME" type="xs:string" length="512" /> 
<item name="LEVEL10_ID" type="xs:string" length="2002" /> 
<item name="LEVEL10_NAME" type="xs:string" length="512" /> 
</metadata>
<data>


<row>
<value>5</value> 
<value>5</value> 
<value>Global Root</value> 
<value>10</value> 
<value>Company</value> 
<value>100001</value> 
<value>Customer</value> 
<value>100002</value> 
<value>Customer Site</value> 
<value>120000</value> 
<value>Location</value> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
<value xs:nil="true" /> 
</row>
</data>

Definitely agree with Christopher's answer about not having a function per depth. You should also not be returning a separate column for each level. The parent Id technique works well, or if you're using Microsoft SQL Server 2008, it has built in support for hierarchy using the HierachyId type (technet.microsoft.com/en-us/library/bb677290.aspx) — Makotosan
– Makotosan, Commented Feb 21, 2012 at 17:01

C.S. · Accepted Answer · 2012-02-21 16:43:23Z

When you are building a tree structure, it is important to use recursive functions. You should never build a function per depth in the tree.

Returning a raw query from the server to the client as text is a little rough. I would suggest putting your query into JSON or XML, in order to leverage the tools in place for doing that. There is no reason to manually parse text responses back into data structures.

In best practices the database (content) and client (view) never talk directly with each other. That is why you have not found any useful information on doing it that way.

Once you have defined your dev environment, such as running a LAMP (Linux, Apache, MySQL, PHP) server, then we can move on to addressing the data structure itself being stored in the table:

ID | Parent | Type | Name | Target
1 | 0 | Folder | "Root" | null
2 | 1 | Document | "Read Me" | "SomePath/Readme.txt"
3 | 1 | Folder | "My Documents" | "SomePath/My_Documents/"
4 | 3 | Document | "Black Book" | "SomePath/My_Documents/Black_Book.txt"

I'm not a DBA, but if you look at the "Parent" column, you can see that any node can have a child, so use business logic to prevent unwanted things from happening, such as a Document having a child. Who knows, maybe your business logic is to allow a document to have type->Permission children associated with them. Either way, do not confuse tree logic with business logic.

Now your Query result looks more like a tree query should. You have 3 typical options:

1) Pass it through a server side template and output the HTML to the client for rendering. 2) Evaluate the query into an XML tree and pass it to the client side template. 3) Evaluate the query into a JSON object and pass it to the client side template.

Once you have all of this stuff knocked out, then we can address the last part of the equation, attaching the response to the DOM.

$('someLocation').append(response);

I suggest using server side template generation for now. It is the oldest and has years of documentation.

I edited my initial post. I put the query into XML and I posted an example what resulted. Not sure what to do with it since that tags are vague. I do not plan on having a function per depth. I was just using that as an example. I have created functions that take the Parent's name and the Child's name as parameters and then append them. I am just stuck on how to grab the data.

btilly · Accepted Answer · 2012-02-21 16:51:28Z

You have two issues.

Getting data from SQL to JavaScript.

Typically you have to dynamically generate a page on the server side in a language like Perl, Python, Ruby, or PHP. (Or even JavaScript if you're using Node.js, but it still won't be running in the browser.) This can be done either when you generate your page, or through a technique like AJAX. I'll say no more about this because you'll find a lot about it online in lots of places.
Using the returned data, make a tree structure.

I would have a hash/dictionary/JavaScript object mapping the ids of all of the nodes to their places in the tree. Then for each node, you can find its parent id object, and append the new node as a new object, also adding it to the hash. When you are done, you'll have a nice tree. (This can be done in a simple loop.) This generation of the tree structure can be done either on the server side in your server side language, or on the client side in JavaScript.

Collectives™ on Stack Overflow

How to populate a Javascript Dynamic Tree with data from an SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related