[nigeltufnel]

I'm a newcomer to the Grocery CRUD system and have had some initial success over the past few days getting it operating with our existing database. What I am encountering now is a major roadblock working with more complex database operations. To be specific, we have a table with hierarchical data that I cannot get to correctly update (or add).

I am using the modified pre-order tree traversal (MPTT) algorithm to represent this hierarchy in the database. I have even implemented the "insert node", "move node", and "delete node" operations in stored procedures. For those not familiar with MPTT, each row can be viewed as a node in a tree. Left and right values are maintained on each node to represent its position in the tree. The impact is that an operation on one node, such as 'insert', 'delete', 'change parent' affect the left and right numbering of [u]other[/u] nodes. The basic CRUD functionality does not handle this.

I considered using triggers to invoke these stored procedures. Unfortunately, I am using MySQL and it doesn't allow operations by a stored procedure on the same table that invoked the trigger:
[indent=1][font=courier new,courier,monospace]ERROR 1442 (HY000): Can't update table 'client' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.[/font][/indent]

Where this leaves me is with two apparent paths:
(1) Add/Edit this table with GCRUD [b]AND[/b] update the left and right values
[b]OR[/b]
(2) Override the existing GCRUD insert, update, and delete operations to invoke stored procs instead of operating directly on the subject table.

To #1, I expect this would require the use of an MPTT model for codeigniter. Selecting and integrating one with GCRUD is beyond my current knowledgebase.

To #2, if I could specify the SQL for the insert, update, and delete operations in Grocery CRUD, I would be able to invoke the existing stored procedures and should have a workable solution.

Ultimately, what I want is to have a CRUD system that can handle the MPTT data. If anyone has had experience with this or any suggestions, they would be greatly appreciated.

Last, here is my environment:
[indent=1]LAMP: CentOS 5.6, Apache 2.2, MySQL 5.0.95, PHP 5.3.3[/indent]
[indent=1]Codeigniter: 2.1.2[/indent]
[indent=1]Grocery CRUD: 1.3.1[/indent]

[victor]

HI! Maybe it will help you http://www.phpkode.com/scripts/item/nested-sets-db-tree/.

[nigeltufnel]

Thanks for the response. I did take a look at that project, but am unclear how that ties into GCRUD - I assume at the model layer.

I do have all of the logic in place in the database itself, in the form of stored procedures. I just cannot figure out how drive that SQL in favor of the automagic being performed by GCRUD directly on the 'subject' table.

Thanks.

[victor]

How do you delete a node? delete from `table` where `id`='4'? or delete from `table` where `left`=>'4' AND 'right'<='30'.
I just did not use nested sets

[nigeltufnel]

More like the latter, except the left and right values have to be updated for the nodes right of the deleted node. (That fills in the gaps). Here is one such explanation of how they work: http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

I have seen nested set models for codeigniter, but it is beyond my understanding how they would integrate with Grocery CRUD. Since I have the nested set operations in the database layer (insertNode, moveNode, etc.), I figured it would just be simpler to call that SQL, as opposed to introducing a PHP framework model that has the same functionality, in effect.

Thanks.

[victor]

look! for example:
this is a simplified example of a delete operation:
Sorry, my English is bad.

class management extends MY_controller{



  function operations(){



    if($this->input->get(delete))

    {

       $id = $this->input->get(delete);

        your actions with a database...

    }



     ....

     //1) use the method column to create columns for your operation

     $crud->columns('title', 'descriptin', ect..., 'DELETE').,

     //2) use callback_column to make a link for the action "DELETE" in this column.

     $crud->callback_column('DELETE',array($this,'_callback_column_delete'));

     .....

  }



   function _callback_column_delete($value, $row)

   {

      return "<a href='".site_url('admin/management/operations/?delete='.$row->id)."'>delete</a>"

    }

}

helped you?

[nigeltufnel]

Thank you. This is getting me heading in the right direction. I haven't gotten the delete tied in neatly, however, I believe I can run with that part.That leaves the two other actions, "Add" and "Update".Both actions operate on a single record and effectively use the details interface. (See attached screenshot. The 'lft' and 'rgt' values are hidded from display). The main difference between the two is the 'save' operation. The "Add" is equivalent to an "insert node" operation, while the "Update" action is equivalent to a "move node" operation. Much like the custom "Delete" operation you laid out, I would like to know if it is possible to replace the "[Save|Update changes]/[Save|Update] and go back to list" functionality with my own.I do appreciate the help and am now more optimistic that my goal can be reached with Grocery CRUD.[attachment=301:Screen Shot client edit.png]

[victor]

show this table structure

[nigeltufnel]

Here is the subject table.


[font=courier new,courier,monospace]mysql> desc client;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| client_id | mediumint(5) | NO | PRI | NULL | auto_increment |
| client_name | varchar(75) | YES | | | |
| client_phone | varchar(30) | NO | | | |
| client_phone2 | varchar(30) | NO | | | |
| client_redirect | varchar(100) | NO | | | |
| client_url_name | varchar(50) | NO | | | |
| client_redirect2 | text | YES | | NULL | |
| client_image | varchar(100) | YES | | NULL | |
| lft | int(11) | NO | | NULL | |
| rgt | int(11) | NO | | NULL | |
| parent_id | mediumint(5) | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)[/font]



Purists will take note that the "parent_id" column is not required. It was not in my original schema and I just added it to make it easier to display the parent company with Grocery CRUD.

[victor]

[quote name='nigeltufnel' timestamp='1348759793' post='3650']
Purists will take note that the "parent_id" column is not required. It was not in my original schema and I just added it to make it easier to display the parent company with Grocery CRUD.
[/quote]
Cool, this help us.

[victor]

try this for action "add":
use the method callback_add_field

$crud->callback_add_field('paren_id',array($this,'show_tree'));

callback function:

function show_tree()

{

//get a tree

$tree = ..... your code for getting tree ;

html.="<select name='paren_id'>";

if($tree)

{

foreach($tree as $item)

{

$html.='<option value="'.$item['client_id'].'">'.$item['client_name'].'</option>';

}

}

html.="</select>";

return html;

}

helped you?

[nigeltufnel]

Victor,

Interesting you should post that. I implemented such a callback in my early development that did populate an drop-down menu of the clients (to establish the parent-child relationship). It did work to the extent of displaying a drop-down correctly.

Here is the kicker. I cannot save my changes. When I click on "Update changes", I see a very brief notification near the Update/Cancel buttons "Loading, updating changes...". However, the database update fails as GCRUD or CI. I know this is sort of vague and I don't have much to go on except for an error on the Firebug console. (See attached screen shot for that). I wouldn't pay attention to the second error about JSON.parse: unexpected character, since I believe it is a side-effect of the previous error.

If I just knew how to override the update operation, I believe this would be a non-issue. I suspect such changes would be made in the model, but that is beyond by knowledge base.

[victor]

only an administrator can add, edit and remove these data or users have a permissions for this operation too ?

[victor]

Is it necessarily to use these triggers? without triggers I can create code for this operations. I think that with triggers you need to create a new model.

[nigeltufnel]

By

[quote]Is it necessarily to use these triggers?[/quote]

are you referring to the add/update operations in Grocery CRUD or database triggers?

When I said
[quote]If I just knew how to override the update operation, I believe this would be a non-issue.[/quote]
what I should have said is how to I work around the default update operation OR make the default update operation work with a nested set model for CI.

If you are referring to database triggers, I actually don't have the stored procedures invoked by triggers, because the stored procedures operate on the same table as the one invoke the trigger (the 'client' table).

Thanks again.

[victor]

Tell me, do you need to move these nodes?

[nigeltufnel]

Yes, I do need to "move" the nodes.

What the move really entails is changing the parent-child relationship.

[victor]

just in this structure there are many types of movement! how many levels of nesting you will? do you have skype?

[victor]

http://pastebin.com/gAN1DZMb

[nigeltufnel]

I had a sense that a model would be the solution, but wasn't sure how to implement. The 'Nested_set' class you referenced on pastebin looks great.

However, I am still left wondering how to tie this into Grocery CRUD.

I understand how my schema fits into the Nested_set model (as follows), but am not sure how to use this model with Grocery CRUD.

class Nested_set {



	    private $table_name;				 <=== client

	    private $left_column_name;		   <=== lft

	    private $right_column_name;		  <=== rgt

	    private $primary_key_column_name;    <=== client_id

	    private $parent_column_name;		 <=== parent_id???

.

.

.

Thanks again. I do feel like a solution is just around the corner.