In this Tutorial you will learn how to create a Tree Grid, as shown in the below screen shot, without having to use a connect by clause. The data for the below tree comes from different tables at different levels based on the parent node.



Define a Datasource with all the required attributes. For SmartFolders, since the data is coming from multiple different tables, we just define a dummy datasource with the required attributes and handle all the queries in the Pre Query Script.



Define a Grid and set all the properties requires for rendering a Tree as shown below. Check the Tree Column layout data property for the displaytext column in the Grid.

Note: "Tree Child View Attribute" & "Tree Parent View Attribute" properties need to be set for a Tree that will be used to generated a nested SQL with connect by clause. In this case since we are not going to use connect by clause, we created two dummy attributes "Parent" & "Child" in the above datasource for these properties.



Specify the Pre Query Script similar to the one below
import com.rapapp.platform.client.mdata.RPCModelData;

RPCModelData parent = (RPCModelData) data.getParent();
StringBuilder query = new StringBuilder();

if (parent == null) {
  // parent will be null for the very first query of the Tree Grid i.e. ROOT nodes
  query.append("SELECT a.folder, a.folder displaytext, a.leaficon, 'N' isleaf, 'Y' root FROM (");
  query.append(" SELECT 'Projects' folder, 'folders_stack' leaficon FROM DUAL");
  query.append(" UNION ALL SELECT 'Users' folder, 'users' leaficon FROM DUAL");
  query.append(" UNION ALL SELECT 'Status' folder, 'status' leaficon FROM DUAL) a");
  // Note: folder is not defined as an attribute in the datasource as we don't refer it in the Page UI Metadata
} else {
  
  String folder = parent.get("folder");
  Double projectid = (Double) parent.get("projectid");
  Double statusid = (Double) parent.get("statusid");
  String type = parent.get("type");
  if ("Projects".equals(folder)) {
    // expanding a node under the Projects node
    if (projectid == null) {
      // expanding the Projects node
      query.append("SELECT 'Projects' folder, P.PROJECT_ID projectid, P.PROJECT_NAME displaytext, 'folder' leaficon, 'N' isleaf FROM PROJECTS P");
      query.append(" WHERE P.PROJECT_ID IN (SELECT PE.PROJECT_ID FROM PROJECT_EMPLOYEES PE WHERE USER_ID = ?)");
      params.add(userid);
    } else if (statusid == null) {
      // the icon to be used for each status is stored in the STATUS table in STATUS_ICON column
      query.append("SELECT 'Projects' folder, ? projectid, S.STATUS_ID statusid, S.STATUS displaytext, replace(S.STATUS_ICON,'.png','') leaficon, 'N' isleaf FROM STATUS S");
      params.add(projectid);
    } else if (type == null) {
      // No Category,Development,Issue,Question,Task,Testing
      
      // Since this is the last level under Projects return 'Y' in the isleaf column/attribute
      query.append("SELECT 'Projects' folder, ? projectid, ? statusid, a.type type, a.type displaytext, a.leaficon, 'Y' isleaf FROM (");
      query.append(" SELECT 'Issue' type, 'bug' leaficon FROM DUAL");
      query.append(" UNION ALL SELECT 'Task' type, 'task' leaficon FROM DUAL");
      query.append(" UNION ALL SELECT 'Development' type, 'construction' leaficon FROM DUAL");
      query.append(" UNION ALL SELECT 'Testing' type, 'tag_label' leaficon FROM DUAL");
      query.append(" UNION ALL SELECT 'Question' type, 'question' leaficon FROM DUAL");
      query.append(" UNION ALL SELECT 'No Category' type, 'document' leaficon FROM DUAL) a");
      params.add(projectid);
      params.add(statusid);
    }
  } else if ("Users".equals(folder)) {
    Double uid = (Double) parent.get("userid");
    if (uid == null) {
      query.append("SELECT 'Users' folder, u.user_id userid, u.display_name displaytext, 'user' leaficon, 'N' isleaf FROM RA_USERS u")
        .append(" WHERE u.USER_ID IN (SELECT DISTINCT P.USER_ID FROM PROJECT_EMPLOYEES P")
        .append(" WHERE P.PROJECT_ID IN (SELECT PE.PROJECT_ID FROM PROJECT_EMPLOYEES PE WHERE USER_ID = ?))");
      params.add(userid);
    } else {
      query.append("SELECT 'Status' folder, ? userid, S.STATUS_ID statusid, S.STATUS displaytext, replace(S.STATUS_ICON,'.png','') leaficon, 'Y' isleaf FROM STATUS S");
      params.add(uid);
    }
  } else if ("Status".equals(folder)) {
    // expanding a node under the Status node
    query.append("SELECT 'Status' folder, S.STATUS_ID statusid, S.STATUS displaytext, replace(S.STATUS_ICON,'.png','') leaficon, 'Y' isleaf FROM STATUS S");
  }
}
fullSQL.append("SELECT X.*, count(1) over() RA_TOTAL_ROWS FROM (").append(query).append(") X ORDER BY displaytext");

Location

101 California Street, Suite 2710
San Francisco, CA 94111


440 N. Wolfe Rd.
Sunnyvale, CA 94085


Office 11, 5th Floor, Building 9,
Mindspace IT Park
Hyderabad 500081

p: +1 (844) AT CloudIO (844-282-5683)
f: +1 (650) 300-5247 | e: sales@cloudio.io
© 2009, 2017 CloudIO Inc. | Terms of Use | Privacy Policy