How to format nested set table data to work in JsTree?

Hi Vince

So, with you mentioning # for the parent then you would seem to be trying to use the 'Alternative JSON format' as described in the docs.

The difference with this format is that the data is not expected to be nested. The javascript will do the nesting for you.

Your back-end code appears to be trying to create a nested set however.

You should get all your categories and then render them into the correct (flat) json fields.

But to start, I would first create a json string in the browser and make that look like you need the tree to look, then move that json block to the controller and serve it as a json response, then finally work out how you can convert your php model into that json block in the back end.

At the moment you are trying to do it all at once, but I think you need to gradually increase your understanding.

Hi Snapey!

Thank you for your response. You are the only one to offer some ideas. No one on S.O. has replied either. I will take your ideas to heart.

To clarify: my categories are all encoded using MIke Hillyer's nested set model. I have all queries and views working with them now. There is no turning back now. :o)

I agree that using the "Alternative" flat file method is a problem. So why did I use it? Because I found an old post on SO which suggested I do so, despite having a nested set. Made no sense to me either, but it had a green answered tick next to it, so I have it a go. So, yesterday, I tried the nested set approach using AJAX as described in JsTree and presented above, which is still giving me the same problem. The only postive is that jsTree is trying to load and run, as I get the progress wheel spinning endlessly. :o)

Ok ... all that said, I am now of the opinion that the real source of my problem lies with my query (above). This query produces an array of objects, which I later encode to JSON.

Sample from Query

array (size=20)
  0 => 
    object(stdClass)[525]
      public 'id' => int 1
      public 'text' => string 'Root' (length=4)
      public 'parent' => null
  1 => 
    object(stdClass)[569]
      public 'id' => int 22
      public 'text' => string 'Materials' (length=9)
      public 'parent' => string 'Root' (length=4)
  2 => 
    object(stdClass)[570]
      public 'id' => int 42
      public 'text' => string 'Roofing' (length=7)
      public 'parent' => string 'Materials' (length=9)

Sample Json

[{"id":1,"text":"Root","parent":null},
{"id":22,"text":"Materials","parent":"Root"},
{"id":42,"text":"Roofing","parent":"Materials"},]

As you can see the parent for Root is null as it should be. However, what befuddles me is that JsTree states that when using the "nested" approach with Json, the required data format of the json, no specific fields are required, JsTree takes care of things. Nevertheless, I formated the data to include id and text, as that is suggested by jsTree. Perhaps the parent should be omitted from the data set ? More over, There is nothing I can see in Hillyer's article which explains how to deliver the whole tree without the Root parent being null. Or indeed, I am not sure how to construct a query without the parent being used.

Perhaps I am completely on the wrong track. Don't know ...at the moment I am shooting in the dark.

Does any of this clarify things in anyway? Many ,many thanks as always !!

@ snapey - Making progress !! I have as you suggested. I took a step back, and wrote out some simple data into my js, and see what happens. This works, and it gives me the tree as depicted in the image link.

Screen Shot of this

Manually Created Json

   $('#jstree').jstree({
                'core' : {
                    'data' : [
                        { "text" : "Materials", "children" : [

                            { "text" : "Roofing",
                                    "children":[{"text":"Asphalt","children":[{"text":"Achitectural"},{"text":"Laminated"}]},{"text":"Cedar"},{"text":"Rubber"}]

                                },

                                { "text" : "Hardware",
                                    "children":[{"text":"Fasteners","children":[{"text":"Bolts"},{"text":"Screws"},{"text":"Anchors"}]}]
                                }

                            ]
                        },

                        { "text" : "Services", "children" : [{"text":"Sanitation"}]}

                    ]
                }
            });

So - the next challenge I have is how am I going to query a nested set DB table such that it gives me the Json required? I am on the right path thanks to you, however, Any ideas ????

I must admit, when I have done this before, I just used regular relationships and could rely on eager loading relations.

eg

$model = Model::with('children.children.children')->whereNull('parent_id')->get('id','name');

This will only go to 4 levels.

Model has a children() relationship

        public function children()
        {
            return $this->hasMany(Model::class)
                                   ->select('id','name','parent_id');
        }

Its a good few years ago though so I might be mistaken.

Then, if you have this structure, you can map over it, just pulling out the values you need if the structure does not suit.

I tested this with a model called 'Material'

There were a couple of typos in the last post.

class Material extends Model
{
    public function children()
    {
        return $this->hasMany(Material::class, 'parent_id')
            ->select(['id', 'name', 'parent_id']);
    }

}

then query the table

$model = Material::with('children.children.children')->whereNull('parent_id')->get(['id','name']);

then cast to json

$model->toJson();

produces this json;

[
  {
    "id": 12,
    "name": "Materials",
    "children": [
      {
        "id": 1,
        "name": "Roofing",
        "parent_id": 12,
        "children": [
          {
            "id": 2,
            "name": "Asphalt",
            "parent_id": 1,
            "children": [
              {
                "id": 3,
                "name": "Architectural",
                "parent_id": 2
              },
              {
                "id": 4,
                "name": "Laminated",
                "parent_id": 2
              }
            ]
          },
          {
            "id": 5,
            "name": "Cedar",
            "parent_id": 1,
            "children": []
          },
          {
            "id": 6,
            "name": "Rubber",
            "parent_id": 1,
            "children": []
          }
        ]
      },
      {
        "id": 7,
        "name": "Hardware",
        "parent_id": 12,
        "children": [
          {
            "id": 8,
            "name": "Fasteners",
            "parent_id": 7,
            "children": [
              {
                "id": 9,
                "name": "Bolts",
                "parent_id": 8
              },
              {
                "id": 10,
                "name": "Screws",
                "parent_id": 8
              },
              {
                "id": 11,
                "name": "Anchors",
                "parent_id": 8
              }
            ]
          }
        ]
      }
    ]
  },
  {
    "id": 13,
    "name": "",
    "children": []
  }
]

You will note that there is some small problem with requesting children when there aren't any. An empty array is the result. Not sure if this will glitch jstree

Thanks ... you have helped to clarify my thinking. I have found some SQL on Mike Hillyer's website, who is very much the God of this technique. From what I have read "nested sets" are considered the "correct way" of doing things, as the alternative, the "adjacency model", apparently relies upon recursion, which is apparently frowned upon.

The SQL will find the children of a node if you select it. Which means the user is only presented with the top layer of the tree when the page loads, and they would have to select each branch to dig down. Might be ok, not optimum though. I never though something we take for granted in Windows and IOS, is so complicated.

I might need some help in in Laravel to create an array of objects, embedded inside a larger array of objects, embedded inside a larger array of objects. So a kind of multi-dimensional array of objects as depicted above. Sounds terrifying I know.

Our posts crossed. Your json is exactly what I need. But I need it from a nested set model. Or I need to be able to reformat the data coming off my table to fit your approach. Sending you my table in case you have a "snapey brainwave" Thanks !!

How about this;

https://github.com/etrepat/baum

Brilliant !!! I will study this later. Gotta go out with the Mrs. I have managed to find a query which will present the top levels, ie Materials, Services. And then I guess, I could just run another query each time the user clicks on a node. In Linux that is how trees work. However I will give this Baum thingee a good look over. I'lll let you know how I get on tomorrow. It's after 12 am for you !!

Cheers !!

Baum appears to use the same table layout, and has commands like;

$tree = Category::where('name', '=', 'Books')->first()->getDescendantsAndSelf()->toHierarchy();

to dump out the entire hierachy

I forgot it was Easter weekend and got nothing done.

Embarrassing question time: I have loaded Baum and have been trying things out with Tinker. It has some very useful queries built into it, an indeed you are right, my testing so far shows me that it works with my existing controllers and JS. However, getting the nodes into JsTree is a challenge yet to come :o)

Dumb question: When using Tinker, this works fine:

use App\Models\Category

$child2->makeChildOf($root);

BUT .. when I want to use the same method in my controller it fails even though I have use Baum/Node in my controller. However, the below does work from inside my controller.

$child2 = Category::create(['name' => 'Child 2']);

I guess that works because I am statically calling the create method.

Ok .. so I try another approach. I put the $child2->makeChildOf($root); method into my Category Model as recommended by the user docs, wrapped in a function called createCategory. In the model I also have use Baum/Node ( or else Tinker would not work) and then call that function like so:

$this->categeory->createCategory();

I get an error back.

So - How the heck do I call this kind of method from within the controller. what am I doing wrong here yes, I have initialised $this->category in the constructor.

Any ideas ??? Many thanks as always!!

What are you trying to do? Which bit are you stuck with?

I'm stuck with how to make $child2->makeChildOf($root); work inside either my controller or my model.

When I put it into my model wrapped in the function called createCatgeory() I get an error back:

Call to undefined method App\Models\Category::makeChildOf()

If I substitute the generic code with real values, I get a different error.

Error Message

Call to a member function makeChildOf() on string

So, here is my real controller:

   public function createBaumCategory(Request $request){

        $this->validate($request, [
            'parent_category' => 'required',
            'new_category' => 'required|max:20',
        ]);

        $category = $request->input('parent_category');
        $value = $request->input('new_category');
        $value = Category::create(['name' =>$value ]);
        $this->createCategory->addNode($value,$category);
        Flash::success(' Your new category has been created.');
        return redirect('nested_categories');


    }

My Real Model:

class Category extends Baum\Node
{
    protected $table = 'categories';
    protected $guarded = ['id'];
    public $timestamps = true;
    protected $primaryKey = 'id';

public function addNode($value, $category){

    $value['name']->makeChildOf($category);

}

}

As a guess

   public function createBaumCategory(Request $request){

        $this->validate($request, [
            'parent_category' => 'required',
            'new_category' => 'required|max:20',
        ]);

        $root = Category::findOrFail($request->parent_category);

        $root->children()->create(['name' =>$request->new_category]);

        Flash::success(' Your new category has been created.');
        return redirect('nested_categories');

    }

(and nothing in model)

or using the makeChildOf method

   public function createBaumCategory(Request $request){

        $this->validate($request, [
            'parent_category' => 'required',
            'new_category' => 'required|max:20',
        ]);

        $root = Category::findOrFail($request->parent_category);

        $child2 = Category::create(['name' => $request->new_category]);
    $child2->makeChildOf($root);

        Flash::success(' Your new category has been created.');
        return redirect('nested_categories');

but thats just from reading the docs, and my interpretation, and assumes that parent_category is an ID and not a name.

If you are passing in the name for the parent then the root element will have to be found as;

    $root = Category::where('name', $request->parent_category)->first();

I think the main problem you are having is not using a category for root and trying to use its reference not the object.

Many Thanks Snappey !!!

The mistake I made is in not using ID's with Baum. So in order to fix this, I am going to have to rebuild much of the JS which drives my views, as much of the views are constructed out of JS. Major Pain !! I'll get there ... slowly ... Have a good weekend !! I'm sure you will hear from me again next week :o) Cheers !

i dont understand why you would need to change much? Check the other post also.

Laracasts us very slow to deliver emails ... like 1 hour! Just saw your last one. Umm .... embarrassing, but I don't understand your other post.

None of my categories in my views have an id. So, that has to be built, or I guess, I could kludge it, and look up the ID with a query. Perhaps I'm doing things wrong, or over engineering things? Have a good one. Gotta walk the dog now. Cheers.

Its not essential to pass ids to the controller. You can do it with the category name

change

$root = Category::findOrFail($request->parent_category);

to

$root = Category::where('name',$request->parent_category)->first();

The point is, whenever you are manipulating the tree you need to be using model objects - not strings

Use below code for achieve your response for JsTree.

<?php 

public function getTree()
{
    $nodes = $this->category->orderByDepth();
    // here need pass to query result array 
    $arr = $this->xyz($nodes);
    return $arr;
} 

// Function for achieve response.

function xyz($parents,$parent_id = 0){
    $res = [];
    foreach ( $parents as $parent ) {
        if ( $parent["parent_id"] == $parent_id ){
            $children = $this->xyz($parents, $parent["id"]);
            if ($children) {
                 $parent["children"] = $children;
            } 
            $res[]=$parent;
        }
    }
    return $res;
}

Note below things for above function

• This response base on the value of parent_id.

• The condition is: when parent_id is equal to id, then the parent_id is considered as children of the id.

• Please note that both parent_id and id are stored as incremental values in my function.

Hope this will work for you

@snapey I'm back, Taxes done! OK - first the good news, your suggestion works !!! Hallelujah!

Now for an embarrassing question. When the original error message said that I could not use strings I went to PHPStorm debugger and looked at the contents of $root. Storm said it is an array. So, that freaked me out. Hence my idea that I had to rebuild everything.

Now - more embarrassing questions: with Storm debugger, I look now into the new contents of $root = Category::where('name',$request->parent_category)->first(); and I see that the original of $root is still an array. Not an real object like this { } anywhere. Also there are mountains of values inside $root. So - a bit confused there.

Lastly, Specifically, How in the heck is laravel so clever as to work out that of all the possible values inside $root, when used inside $child2->makeChildOf($root); refers to orginal ['name=>VALUE]`. I can send you screen shots of that helps.

I have tried googling object models and the info given does not appear to be relevant to this discussion.

I appreciate that when using Eloquent, category::where() and category::create returns a model instance, but I don't see how Laravel is clever enough to find the VALUE. Also I can now see that when you referred to an object, you did not actually means an object in the real { } sense.

It's probably pdo and mysql knows what to do. When you pass for example an "id" (int) in database through a POST request, but the column in mysql is an int, mysql handles it for you.

And pdo binds it properly,

$id = $someid;  // say passed in the request

// later when binding

[':id' => $someid]

I don't remember the term for that, but it's not strict binding.

Pdo also has binding values where you have to put type.:

Fro pdo manual

quote

$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);

unquote

Any way PDO and MySql know what to do.

C# and JAVA is not as relaxed on this stuff as PHP.

Of course Taylor handles a lot for us in the background. Deep in the vendor folder.

when I referred to an object, I meant a php object not an array [] or a json object {}

baum wants to link the objects together which it does with their ids (primary keys)

Sure understood. I guess I am looking for some education as to how Laravel is so clever as to find the correct value from the Category instance, which is returned as an array. I don't want to be asking you a similar question next month :o)

Any ideas?

Many Thanks !!

Laravel is not doing anything. The package knows how to use the primary keys to link the models together. it knows what id is on the left and what on the right. I assume it just uses the id, but it should be asking eloquent for the primary key field.

Can you tell me how to design database?