Development Tips

How to Set Up Recursive CTE Filtering Using Logi Info

By Trevor Denning
Share on LinkedIn Tweet about this on Twitter Share on Facebook

Filtering large datasets is resource intensive and time consuming on the server side. This can lead to slow application performance—and, ultimately, dissatisfied customers and end users.

At Logi Analytics, we recommend solving the problems of slow page load times and lagging performance by:

  • Implementing Common Table Expression (CTE) in Tree Data Structure using Logi Info
  • Control runs client-side
  • Using Dynatree (jQuery plugin) to display tree data results
  • Add node to list using TagIt (jQuery plugin)
  • Typeahead to find all node that match keyed text. Then traverse node to find connecting parent and children nodes.

Recursive CTEs are special since they reference themselves, which makes them good at working with hierarchical data such as organizational hierarchies, file systems, inventory systems, and more.

>> Update your embedded dashboards: Get the Blueprint to Modern Analytics <<

Since a recursive CTE allows you to retrieve relational data in a hierarchical format, it helps the end user navigate the data. The hierarchical dataset establishes parent-child relationships between entities.

Recursive CTEs help retrieve relational data in a hierarchical format

By running the recursive CTE on the client-side, we have been able to reduce page load times from an average of 8 seconds per page to less than 2 seconds per page.

Dynatree (or a similar jQuery plugins) displays the data in an intuitive way using lists. Each row in the list can contain child rows to create an expandable-list, or tree. This makes it easy to display and navigate deep layers of hierarchical data.

The Tag-it plugin is a configurable tag editing widget that allows typeahead filtering. In Logi Info, we created a quick “add” button that updates the tags and results, while placing the IDs in a hidden element.

Check out an example of recursive CTE filtering using Logi Info:

What does recursive CTE filtering feature in Logi Info?

Our unique implementation of recursive CTE filtering at Logi Info allows for:

  • Parent-Child Display: When searching for Child, the parent is displayed. For example, if a user searched for CINNAMON in the example linked above, they will see the hierarchy shown below, with the REGULAR CINNAMON RAISIN BAGELS highlighted.

Sample tree data with parent-child display

  • Highlights Text Matching Search: All instances of CINNAMON in the example above will be highlighted in the appropriate tree node.
  • Shows Number of Results: The number of results matching the search text will be displayed at the top.
  • Autocomplete Support: Tag-it JQuery plugin allows for adding quick filtering criteria and autocomplete support.
  • Lazy Load Support: Supports delayed loading of tree nodes.

What should I consider in my design?

  • Make sure you understand your data structure really well. Although most tree data is standard, the way the data relates to each other may get confusing.
  • Create a Unique Identifier for each node element.
  • Create Data Definitions to get the Hierarchical Data and store them in a local JSON object.
  • Use jQuery to filter the data results.

What Are Your Variable Names?

Variable names for recursive CTE data filtering

SQL Code running the queries

The four SQL code snippets below illustrate the recursive CTE statements for each scenario:

  • Retrieve all records to (n) level
  • Expand category
  • Recursively get tyepahead data
  • Lazy Load

1. Retrieve All records to (n) Level:

This SQL code retrieves all records to (n) level:

   SELECT distinct [key], title, parent, [level]
   FROM tree_data
   WHERE [level] <= '@Request.Level~'
   order by [level], title

1. Expand Category:

When a user clicks a category, this SQL retrieves the data:

   SELECT distinct [key], title, parent, [level]
   FROM tree_data
   WHERE parent = '@Request.ID~'
   AND [level] = '@Request.Level~'

3. Recursively get typeahead data:

This SQL code recursively gets typeahead data:

   ;WITH RecursiveCte AS (
   SELECT H1.[Level], H1.[key], parent, H1.title FROM tree_data H1
   WHERE title LIKE '%@Request.Description~%'
   SELECT RCTE.[level] - 1 as Level, H2.[key], H2.parent, H2.title FROM tree_data H2
   INNER JOIN RecursiveCte RCTE ON H2.[key] = RCTE.parent
   SELECT distinct [key], title, [level], parent
   FROM RecursiveCte
   order by [level], title

4. Lazy Load

The lazy code snippet allows delayed loading of tree nodes, which means the user reads the child nodes only when their parent is expanded:

   SELECT distinct [key], title
   FROM tree_data
   WHERE [key] in (@Request.FilterBy~)

Questions or concerns? Feel free to email us.


Originally published February 2, 2017; updated on August 9th, 2017

About the Author

Trevor Denning is a software engineer and web developer at Logi Analytics.