Skip to main content

Query Design for TreeView

This document provides the query required for the TreeView property used in objects Explains how to prepare the set. The example passed in the attachment shows users connected to departments in the tree It is based on three queries prepared for a TreeView that lists them in the structure.

1. General Logic​

A total of 3 queries are prepared for a TreeView definition. These three queries work together to help the root of the tree (root) nodes, the child elements of these nodes, and the single single structure of the entire structure when necessary. It allows it to be fetched at a time (general/main query). The naming of the three queries is determined by the name of the main query. Whatever the name of the main query is, the other the two queries continue with the following additions to this name:

• Main query (General query): ''<SORGUADI>'''''

• Root query: '''<SORGUADI>''''Root

• Childs query: '''<SORGUADI>''''Childs

For example, in the example passed in the attachment, the name of the main query is DEPTUSER, so other queries It has been named DEPTUSERRoot and DEPTUSERChilds. When designing your own TreeView When you select a name that matches your main query, you can replace the other two queries with the same name, only Root and Childs suffixes.

Note: This naming convention in query names is used when defining the TreeView object properties required for the query to be automatically recognized and correctly matched. Nomenclature rule is not followed, TreeView will not work properly

2. Query Structure and Required Fields​

Each of the three queries should return the following four fields in the same order:

ID: The unique ID of the node in the tree. If the detail is to be linked to the form, this field must be the same as the registration ID of the relevant form.

PARENTID: The ID of the parent node to which the node is connected. At the root level Returns NULL for nodes

DESCRIPTION: Text (tag) to be shown to the user in the tree.

CHILDCOUNT: The number of child elements of the node. If 0 is a indicates that there is no branch.

Note: These four fields must be present in all queries, and the field names (ID, PARENTID, DESCRIPTION, CHILDCOUNT) It should be written exactly like this.

3. Tasks of the Three Queries​

3.1 Main Query — QUERYNAME​

A single query can list all levels of the tree (both the root nodes and the elements below them) in, usually by combining it with UNION. Object property in TreeView This is used as the default/global query when enabled.

Example: DEPTUSER

select OSUSERS.ID, OSDEPARTMENTS.ID PARENTID, OSUSERS. FIRSTNAME + ' ' +
OSUSERS. LASTNAME DESCRIPTION,
0 CHILDCOUNT
from OSUSERS, OSDEPARTMENTS
where OSDEPARTMENTS.ID=OSUSERS. DEPARTMENT and OSDEPARTMENTS.ID ```<``````>``` 'system'
union
select D.ID, null as PARENTID, D.DESCRIPTION,
(select count(U.ID) from OSUSERS U where U.DEPARTMENT=D.ID) CHILDCOUNT
from OSDEPARTMENTS D
where D.ID ```<``````>``` 'system'

3.2 Root Query — QUERYNAME Root​

Returns nodes at the top (root) level of the tree. The PARENTID field is always NULL because these nodes do not have a higher level. TreeView appears when it is first opened nodes come from this query.

Example: DEPTUSERRoot

select D.ID, null as PARENTID, D.DESCRIPTION,
(select count(U.ID) from OSUSERS U where U.DEPARTMENT=D.ID) CHILDCOUNT
from OSDEPARTMENTS D
where D.ID ```<``````>``` 'system'

3.3 Childs Query — QUERYNAME Childs​

It works when the user expands (clicks on a node) and allows the selected node to be directly returns its sub-elements. This query takes the ID of the clicked node as a parameter and condition.

Example: DEPTUSERChilds

select OSUSERS.ID, OSDEPARTMENTS.ID PARENTID, OSUSERS. FIRSTNAME + ' ' +
OSUSERS. LASTNAME DESCRIPTION,
0 CHILDCOUNT
from OSUSERS, OSDEPARTMENTS
where OSDEPARTMENTS.ID=OSUSERS. DEPARTMENT and OSDEPARTMENTS.ID ```<``````>``` 'system'
and OSDEPARTMENTS.ID = '```<```?=PARENTID```>```'

The expression '''<?=PARENTID>''' here is used to work with the ID value of the node that the user is extend. It is a parameter placeholder that is automatically changed in time.

TreeView is used to open the detail of the relevant record to a form when a node is clicked If the ID field in the query is to be used, the ID field in the query is exactly the same as the ID of the record to which the form to be opened is associated should be. TreeView opens the detail form based on this record using the ID value of the selected node.

In the example scenario, the ID field of user nodes OSUSERS.ID, the ID field of department nodes field is set to OSDEPARTMENTS.ID. Thus, a user node clicked, the detail form of the relevant user can be opened with the correct ID

Note: If there are multiple different types of nodes in the tree (in the example, both department and user) and each type If it is going to go to a different detail form, the ID fields do not overlap and the correct form for each type It should be checked separately that it has been opened.

5. Nomenclature Summary​

Here's the naming template to follow when crafting queries for your own TreeView:

QUERYNAME: Main query — returns the entire tree (root + childs) with UNION.

QUERYNAME Root: Returns only nodes at the root level (PARENTID = NULL). 

QUERYNAME Childs : Returns the child elements of a selected node (where ... ID =
'```<```?=PARENTID```>```').

After these three queries are prepared, the main query is opened in the object properties while the TreeView definition is (''<SORGUADI>) is selected; the system automatically performs Root and Childs queries according to the naming convention. associates as.