Recipe: How to use nested sets (i.e. hierarchical data) in Fabrik

[alastairwalker]

This post may be considered as a sequel to the post http://www.fabrikar.com/forums/inde...k-to-work-with-nested-sets.25741/#post-136803 the result of which was inconclusive.

Out of necessity I have had to recently implement a nested sets model in Fabrik.

This post is a description of the 'recipe' that I have followed in achieving this goal. The recipe is as follows:

Steps to setting up the model:

0. Assumptions made regarding the Fabrik setup:

0.1 Absolutely essential reading before you get started:

http://docs.joomla.org/Using_nested_sets

This article provides a reference for the class and methods description used.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

This article provides a useful example of data to test if the nested sets model you have set up works correctly.

0.2 Plugins that need to be installed:

1. Fabrik List - PHP events
2. Fabrik Element - Calculation

0.3 Class and methods file

Copy the file 'nestedsets.php' into the Fabrik folder:

<root>/administrator/components/com_fabrik/tables/

0.4 Assumptions made regarding your experience of Fabrik.

I assume that you are comfortable in creating Lists, Forms, Groups and Elements. If this
is not the case, you should make use of the extensive tutorials listed on the Fabrik website.
So an 'intermediate' level of experience is assumed.

0.5 The files attached to this post

The files attached to this post all have the .txt extension. To use the files (i.e. as php executable files)
replace the .txt extension with the .php extension.

0.6 The supplied images

There are three supplied images:

a) Image1 showing the list with data entered from http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
b) Image2 showing the form under New (i.e. when Add is used)
c) Image3 showing the form when Edit is used (i.e. when the form is edited).

1. Create the table in the database, using the mysql script

-- Table structure for table `jm32_nestedsets`

CREATE TABLE `jm32_nestedsets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`lft` int(11) NOT NULL DEFAULT '0',
`rgt` int(11) NOT NULL DEFAULT '0',
`level` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(255) NOT NULL,
`alias` varchar(255) NOT NULL DEFAULT '',
`access` tinyint(3) unsigned NOT NULL DEFAULT '0',
`path` varchar(255) NOT NULL DEFAULT '',
`hierarchy_display` text,
`relationship` text,
`list_toc_index` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_left_right` (`lft`,`rgt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Use PHPADMIN for this task. Paste this script into the SQL tab, and execute. The table will be built.
Note: Replace the 'jm32_' with the actual table prefix.

2. In FB, in List, build the List/Form/Group objects using New.

Create the list, under Label enter a name like 'A Nested Set'.
Under List->Data->database table, select the nestedsets table name, as created in 1. (say jm32_nestedsets).
Save the result.
Under List->Data-Group by, select Order by column as 'lft', order Ascending.

3. In FB, in Elements, select the Form filter of 'A Nested Set', and Group filter of 'A Nested Set'.

The elements will be shown.

3.1 Select the element 'parent_id',

Choose the 'databasejoin' plugin; Group: A Nested Set;

Elements->Data, select table: jm32_nestedsets, Value: id; Label: hierarchy_display
Elements->Data-where, Joins where and/or order by statement (sql): order by lft asc

3.2 Select the element 'hierarchy_display'

Choose the 'calc' plugin: Group: A Nested Set;

Elements->More, Calculation: Add the following PHP script:

$result = JTable::addIncludePath(JPATH_ADMINISTRATOR . '/components/com_fabrik/tables');
$table = JTable::getInstance( 'nestedsets', 'Table' );
$row_id = (int) '{rowid}';
return $table->hierarchy_display($row_id);

3.3 Select the element 'relationship'

Choose the 'radiobutton' plugin: Group: A Nested Set;

Elements->Options, set up the following options:

Value: before; Label: Before; Default (no)
Value: after; Label: After; Default (no)
Value: first-child; Label: First Child; Default (Yes)
Value: last-child; Label: Last Child; Default (no)

3.4 Forms setup

There are three php scripts to be set up, using the following files:

Nestedsets-Form-Create-initial-record.php
Nestedsets-Form-New-records.php
Nestedsets-Form-Edit-records.php

These forms must be located in the path: <root>/plugins/fabrik_form/php/scripts/

Settings for each file:

3.4.1 Nestedsets-Form-Create-initial-record.php

Published: Yes
-do-: PHP
In: Both
On: New
Process Script: Before the form data is loaded (onBeforeLoad)

3.4.2 Nestedsets-Form-New-records.php

Published: Yes
-do-: PHP
In: Both
On: New
Process Script: After any images have been uploaded (onBeforeStore)

3.4.3 Nestedsets-Form-Edit-records.php

Published: Yes
-do-: PHP
In: Both
On: Edit
Process Script: After any images have been uploaded (onBeforeStore)

3.4 Groups setup

In the New mode, the following fields must appear in the form:

- parent_id (i.e. to select the Parent Category); and
- title (to describe the item)

The other fields can be added as needed, including alias, access, and path.

In the Edit mode, the following fields must appear:

- parent_id (i.e. to select the Parent Category); and
- title (to describe the item)
- reference (select the type of move action (one of: after, before, first-child, last-child).

Note: In the New action, the default action is 'first-child'.

We will need to hide the reference field in the form on New, but show it on Edit.

To do this, we need to create a special group to hold the reference element. We will call this group 'Nestedsets-

reference'.

We will use Javascript to hide the new group.

But first we need to create the new group, and assign the element 'reference' to it.

3.4.1 Create new group

Use New to create the new group.
Under Details, assign the name of 'Nestedsets-reference' to the new group.
Label: Leave this blank
Form: Select 'nestedsets'
Save and Close.

3.4.2 Assign the element 'reference' to the new group

Select Elements.
Under Select Form: Select 'Nestedsets'
Under Select Group: Select 'Nestedsets'

Click on the 'relationship' element.
Click on Group. You will see two groups listed: 'Nestedsets', and 'Nestedsets-reference'
Select 'Nestedsets-reference'
Click Save and Close. This will save the element to the new group.

Now click on Select Group: and Select 'Nestedsets'
You will find a sing field there 'reference'

3.4.3 Set up to hide the new group on New.

In Elements, select 'id'.

Under Javascript, click 'Add'.
Set up as follows as a predefined action:
Event: 'on load'
If this element: '=='
Value: leave blank
Action: Hide
Element: nestedsets-reference

Note: This simply has the effect of hiding the group when the form is loaded.

We need to make is visible on Edit.

3.4.3 Set up to show the new group on Edit

To show the group, we need to choose an element that will have a changed value on Edit, compared to New.
A good element to choose for this purpose is 'rgt'. When a new record is created the assigned value will be '1'.
(See the default field settings in Section 1.)

In Elements, select 'rgt'.

Under Javascript, click 'Add'.
Set up as follows as a predefined action:
Event: 'on load'
If this element: '<='
Value: 1
Action: Hide
Element: nestedsets-reference

Note: This simply has the effect of hiding the group if the rgt value is still the default value. Once the rgt field has been modified, the group will be shown.

 

[alastairwalker]

Section 2 in the above recipe needs to be extended/revised.

The script omitted the fact that the List PHP Event plugin must be used to manage row deletes, as the standard List method will not take care of the node management, based on JTableNested.

The revised Section 2 description should read:

2. List issues

2.1 Create the list, under Label enter a name like 'A Nested Set'.

Under List->Data->database table, select the nestedsets table name, as created in 1. (say jm32_nestedsets).
Save the result.
Under List->Data-Group by, select Order by column as 'lft', order Ascending.

2.2 List - script to manage row deletes

Since row deletes must be managed under the same framework i.e. using the Joomla JTableNested class and related methods, the List PHP Events plugin must be used to manage deletes.

Go to List->Plugins

Under the textarea of onDeleteRows, add the script:

// Get the array of selected rows

$app = JFactory::getApplication();
$ids = $app->input->get('ids', array(), 'array');

$result = JTable::addIncludePath(JPATH_ADMINISTRATOR . '/components/com_fabrik/tables');
$table = JTable::getInstance( 'nestedsets', 'Table' );

foreach ($ids as $row)
{
$id_node = (int) $row;// load the id value

// Delete the node.

if ($table->delete( $id_node )) {
// echo 'Node deleted';
$table->refresh_toc();// also refresh the table of contents
}
else {
// echo 'Failed to delete node';
}
}

 

[felixkat]

Very nice. I'll work through this later and try and create a demo out of it, probably something for the WIKI as well.

 

[alastairwalker]

Once you have verified the behaviour and found any description gaps, the Wiki is a good destination for it. Things tend to get lost in these posts after a while.

Alastair

 

[felixkat]

The write up was perfect, I think a few images will assist which I am happy to do. Oh and I think there were a couple of Joomla Prefixes to change in the scripts, although this should be fairly obvious when editing.

I will have to do some testing and study the whole nested sets some more before I can tell. I will follow this up though.

It seems to work, although I can't say if it's correct I've placed a read-only list here:- http://fabriking.com/nested-sets

 

[alastairwalker]

Once you have sorted that out, I would like to float an idea for a project to be able to use treeview as a visual model for the list view when JTableNested is applied (i.e. to support nested data/ hierarchical data).

Since my Javascript/ list template/ css skills are still at the 'newbie' stage, I am going to need a lot of help.

If there is a willingness to co-operate in this development, I will start up a multiway 'conversation' with those who show interest.

PS - A useful reference to kick off the conversation is to look at http://www.lateralcode.com/directory-trees-with-php-and-jquery/

PSS - the last post on the matter of the use of treeview as a metaphor was posted in the FB forum was: http://www.fabrikar.com/forums/inde...ree-view-of-data-to-fabrik.18422/#post-100469

Alastair

 

[cheesegrits]

I just wanted to say Thank You for your efforts on this. I don't currently have time to look at folding this in to Fabrik itself, or play around with nested sets, but it is good to know there is a way of getting it done. Nested sets are something we've looked at in the past, but obviously it's a lot of work, and we are always in the position of having to balance the amount of work required to implement any given feature, against the number of people who would use it. This is one of those that's on the cusp ... I think if we did it properly, it may get enough use to be worth while. But right now, with our current workload and "staffing issues" (I'm way off my usual game, pending some serious back surgery) ...

Anyway, just wanted to say thank you for your efforts and contributions to the Fabrik ecosystem.

-- hugh

 

[alastairwalker]

Thank you Hugh for the comments! These are appreciated!
Alastair

 

[nusilmar]

If only i was a "coder" i would be willing to help. Since my coding skills are close to none, the only thing i could help is by testing or wiki writing. But as an user and a sql developer i find this as a major improvement to be applied into fabrik. Your step by step explanation, your coding and your patience ( yes, we all need that to) on adapting this into fabrik must be applauded

A big thank you , Alastair !!!!

 

[alastairwalker]

Your comments are appreciated!
Alastair

 

[lcollong]

A very nice piece of work indeed. I'll test it as soon as I can as it is a recurrent need in projects. Congratulations.

 

[lomcarlo]

3.4 Groups setup

In the New mode, the following fields must appear in the form:

- parent_id (i.e. to select the Parent Category); and
- title (to describe the item)

The other fields can be added as needed, including alias, access, and path.

In the Edit mode, the following fields must appear:

- parent_id (i.e. to select the Parent Category); and
- title (to describe the item)
- reference (select the type of move action (one of: after, before, first-child, last-child).

Note: In the New action, the default action is 'first-child'.

Hi, a very nice guide. I don't understand where I can change the fields that must appear in the form and what do you mean for "New/Edit mode".

Thanks

 

[alastairwalker]

The level of your inquiry suggests that you are new to Fabrik. (Correct?). If so - then you need to begin by going through the tutorials to come up to speed on the features of Fabrik.

Kind regards,

Alastair

 

[m6xmed5]

Hi Alistair,
This post is excellent, I'm looking to implement this structure to a database for a project I'm working on.
I'm having trouble with one of the instructions in the post...

3.4.2 Assign the element 'reference' to the new group

Select Elements.
Under Select Form: Select 'Nestedsets'
Under Select Group: Select 'Nestedsets'

Click on the 'relationship' element.
Click on Group. You will see two groups listed: 'Nestedsets', and 'Nestedsets-reference'
Select 'Nestedsets-reference'
Click Save and Close. This will save the element to the new group.

Now click on Select Group: and Select 'Nestedsets'
You will find a sing field there 'reference'

In particular, the part at the end. Do you mean click on 'Select Group' under the element filter? If yes, my nestedsets group contains all of the elements from the SQL query in the first section except from 'relationships' element
There is no field element called 'reference'.

Any chance you can set me straight on this? I'd like to test this out over the weekend.

Edit - I think I know whats happened...

It's meant to say this;

Now click on Select Group: and Select 'Nestedsets-reference'
You will find a single element there 'relationships'

Please correct me if I'm wrong but it makes sense following through the rest of the steps.