Macrotone Blogs

Macrotone blogs upon Joomla, our products and other matters.
Categories
Tags
Archives
Calendar
Categories:   All Categories
Suggested keywords
x
Font size: + –
  Print

Joomla and JTableNested

4259 Hits

joomla
We recently started addressing a small concern that we had with the handling of Projects in our Issue Tracker component.  Not I hasten to add, a bug, but more a lack of functionality that was desired.  The search functionality was a little limited, and we has implemented some code to sort the projects in the desired order.  Whilst it worked it was not what we would describe as ‘pretty’.  We took the bullet and decided to convert the table from a ‘hierarchical’ format to a ‘nested’ format.

The desire was to

  • improve the search facility
  • to clean up the existing code making better use of supplied Joomla functionality
  • to provide an improved sort ability
  • to introduce ACL group control of the projects (we had already implemented ACL controls upon the issues in an earlier release).

It was decided to make use of a nested sets model.  Using the description given in the Joomla article referred to below: In a nested sets model, each data item is stored as a row in the database table in the normal manner. However, additional columns are present in the table to express the hierarchical relationship between the data items. Each data item is referred to as a node and the collection of nodes can be thought of as forming a tree. Nodes can have zero, one or many child nodes. A node that has no children is referred to as a leaf node. A child node can itself have children and this nesting can carry on to arbitrary depth. The table is assumed to hold a single tree with a single node being allocated as the root node. If there is a need to be able to store multiple trees then one simply makes the root node of each such tree a child node under the single root node and adjusts the code to take this into account. The term "nested sets" is a little inaccurate as a set is, strictly speaking, an unordered collection of objects. In the Joomla implementation the order of nodes under a parent node is preserved and API calls are available to manipulate the order. A better term would perhaps be "nested ordered sets".

JTableNested is a Joomla class that extends the usual JTable class that is commonly used in component such as categories and assets, to name only two.  But what if you wanted to use it in your own components?  Well here it starts getting a little bit tricky.  Yes there is one reasonable article on the official Joomla documentation site, but it doesn’t really (in my opinion) go far enough, and explain what is required and what isn’t.  In our tests we found that the ‘title’, ‘alias’, ‘access’ and ‘path’ fields were not strictly necessary to get the basic functionality working.  Of course the article is a little old, and Joomla has made a lot of changes over recent times, so some of the earlier limitations may no longer apply and are perhaps understandable. Thus the main source of detail involves the reading of the library code and seeing how the base components (categories) use the class.

The second area to address was how to convert an existing hierarchical table to a nested structure.  We discovered a (Generation 5) article which explains a similar situation and provides some sample code to perform the task.  Again quite old but a very useful article despite its age.  Using that code as a base, we have modified the code to run under Joomla and use it to convert one of our component tables to the new ‘nested’ format. 

This was not however the only areas to address since we have several other steps to perform before we can use our code.

  1. The first of these involved creating a ‘root’ entry, which is reasonably trivial.
  2. The second involved populating the ‘level’ field based upon the  parent identifiers that were already present in our original table.
  3. The third step involves running the modified code (obtained as explained above) to create the ‘lft’ and ‘rgt’ fields.
  4. The fourth and final step involves the setting of the ‘root’ node so that it encompasses the whole range.

Performance wise it was very efficient, due mainly to the majority of the work being performed in memory.  With large numbers of records to process (i.e. several thousand) it might not be so efficient.

Having converted the table all that remained was to rework the model and views to make use of the new fields  (and remove the unrequired ‘older’ fields).

Did it achieve what we intended?  Well the answer to that is Yes.  It was about two days work, but after testing we are very happy with the result and intend that it is incorporated in our next release of our Issue Tracker component.

Animated Gifs in banners stopped working
DocBook XML tables

About the author

Geoffrey Chapman

Geoffrey Chapman

Subscribe to updates from author Unsubscribe to updates from author Geoffrey Chapman

An IT professional with a wide experience of IT systems, specialising in Database Management and Security.

Author's recent posts
More posts from author
 
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries