Tree only shows upper level about closuretable HOT 26 CLOSED

At first sight i can see already something that seems wrong: 39 inserts in grades table, same number of inserts in grades_closure table. Closure table doesn't work in this way, are you manually inserting data? Do you mind pasting the controller code you're using to nest categories?

from closuretable.

I did not manually insert them, but when I noticed the first issue I described, I removed the self-referencing entries in grades_closure. Are you saying they should be there?

This is how I moved an item to its parent:

$grade = Grade::find(62);
$grade->moveTo(Grade::find(70));

from closuretable.

Yes, the self referencing entries have to stay there to allow the correct functioning of the methods. Don't touch anything in the closure table, and use only the methods provided, or it won't work at all.
If you want to learn how closure table method works, you can watch this: http://www.percona.tv/percona-webinars/models-for-hierarchical-data-in-sql-and-php, where Bill Karwin, the inventor of this method, explains it comparing it to other techniques.

from closuretable.

Alright, so I've started from scratch, and now when I try to move them to their parent, this happens (the signed position value I mentioned in my original post):

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`database`.`grades`.`position` - 1)' (SQL: update `grades` set `position` = `position` - 1, `updated_at` = ? where `grades`.`deleted_at` is null and `id` in (?) and `position` in (?, ?)) (Bindings: array ( 0 => '2013-10-01 00:38:31', 1 => '73', 2 => 0, 3 => 1, ))

This is how I was moving them:

// Just a range for the id's I wanted to move
$range = range(73, 84);
$grades = Grade::find($range);
$parent = Grade::find(110);

foreach ($grades as $grade):
    $grade->moveTo($parent);
endforeach;

Edit: And thanks for the link, having a look right now.

from closuretable.

@vjandrea, we should implement ClosureTable\DatabaseSeeder or something, I think. It seems that it could simplify things.

from closuretable.

@ben-joostens please, post here, as separate comments, your actions that leading to exceptions or erroneous behaviour. Also I'd like to see stack traces for all of that situations, also as separate comments.

Thanks.

from closuretable.

@franzose Alright, will make work of it tomorrow.

from closuretable.

Alright, here's the step by step.

First of all, this is the database structure I start out with:
https://dl.dropboxusercontent.com/u/139621/01_grades_initial.sql

from closuretable.

I then populate the grades table:

$grades = array(
                array(
                    'title' => 'Eerste Kleuterklas A',
                    'title_short' => '1KA',
                ),
                array(
                    'title' => 'Eerste Kleuterklas B',
                    'title_short' => '1KB',
                )
);

foreach ($grades as $grade):
    $ngrade = new Grade($grade);
    $ngrade->save();
endforeach;

Which gives me a database with these contents:
https://dl.dropboxusercontent.com/u/139621/02_grades_populated.sql

from closuretable.

The next step is where the issue arises. I'll try to move a few items to their parent:

$range = range(1, 12);
$grades = Grade::find($range);
$parent = Grade::find(38);

foreach ($grades as $grade):
    $grade->moveTo($parent);
endforeach;

Which throws this error:

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`deputseknipoog_new`.`grades`.`position` - 1)' (SQL: update `grades` set `position` = `position` - 1, `updated_at` = ? where `grades`.`deleted_at` is null and `id` in (?) and `position` in (?, ?)) (Bindings: array ( 0 => '2013-10-01 12:56:11', 1 => '1', 2 => 0, 3 => 1, ))

It ends up moving 2 items to their parent, which you can see in the export, if I rerun the function, it'll behave the same, add some and then throw an error.
https://dl.dropboxusercontent.com/u/139621/03_grades_after-move.sql

23. Exception
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Connection.php555
22. Illuminate\Database\Connection handleQueryException
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Connection.php528
21. Illuminate\Database\Connection run
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Connection.php355
20. Illuminate\Database\Connection affectingStatement
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Connection.php300
19. Illuminate\Database\Connection update
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Query/­Builder.php1438
18. Illuminate\Database\Query\Builder update
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Query/­Builder.php1472
17. Illuminate\Database\Query\Builder decrement
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Eloquent/­Builder.php288
16. Illuminate\Database\Eloquent\Builder decrement
…/­vendor/­franzose/­closure-table/­src/­models/­Entity.php912
15. Franzose\ClosureTable\Entity reorderSiblings
…/­vendor/­franzose/­closure-table/­src/­models/­Entity.php962
14. Franzose\ClosureTable\Entity performUpdate
…/­vendor/­laravel/­framework/­src/­Illuminate/­Database/­Eloquent/­Model.php1066
13. Illuminate\Database\Eloquent\Model save
…/­vendor/­franzose/­closure-table/­src/­models/­Entity.php845
12. Franzose\ClosureTable\Entity moveGivenTo
…/­vendor/­franzose/­closure-table/­src/­models/­Entity.php822
11. Franzose\ClosureTable\Entity moveTo
…/­app/­controllers/­GradeController.php188
10. GradeController index
<#unknown>0
9. call_user_func_array
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Controllers/­Controller.php138
8. Illuminate\Routing\Controllers\Controller callMethod
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Controllers/­Controller.php115
7. Illuminate\Routing\Controllers\Controller callAction
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Router.php985
6. Illuminate\Routing\Router Illuminate\Routing\{closure}
<#unknown>0
5. call_user_func_array
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Route.php80
4. Illuminate\Routing\Route callCallable
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Route.php47
3. Illuminate\Routing\Route run
…/­vendor/­laravel/­framework/­src/­Illuminate/­Routing/­Router.php1016
2. Illuminate\Routing\Router dispatch
…/­vendor/­laravel/­framework/­src/­Illuminate/­Foundation/­Application.php530
1. Illuminate\Foundation\Application dispatch
…/­vendor/­laravel/­framework/­src/­Illuminate/­Foundation/­Application.php506
0. Illuminate\Foundation\Application run
…/­public/­index.php49

from closuretable.

Just for completeness, here is an export of the database after I run the function which moves the items to their parent again. It has moved 2 extra items, but the position for the four items is now 0,2,4,5
https://dl.dropboxusercontent.com/u/139621/04_grades_after-second-move.sql

from closuretable.

@franzose I hope you have enough information with what I've provided here, if not, let me know.

from closuretable.

I've been playing around with it for a bit, and managed to get a working tree by using the appendChild method instead of moveTo. However, I have a page load of about 12 seconds (constant querying). My grades table has 89 entries, my grades_closure table has 251.

Not sure if this long load time is normal behaviour?

from closuretable.

@ben-joostens, it's strange but I made tests and they doesn't reproduce the behavior you faced. I mean wrong positioning. Test tables are prepared with the following (you can find this code at tests/ClosureTableTestCase.php):

        // since we use SQLite we have to add foreign keys support manually.
        DB::unprepared('PRAGMA foreign_keys = ON;');
        Schema::create('pages', function($table){
            $table->increments('id')->unsigned();
            $table->string('title', 250);
            $table->string('excerpt', 500);
            $table->text('content');
            $table->string('language', 2);
            $table->integer('position')->unsigned();
            $table->timestamps();
            $table->softDeletes();
        });

        Schema::create('pages_closure', function($table){
            $table->increments('ctid');
            $table->integer('ancestor')->unsigned();
            $table->integer('descendant')->unsigned();
            $table->integer('depth')->unsigned();

            $table->foreign('ancestor')->references('id')->on('pages')->onDelete('cascade');
            $table->foreign('descendant')->references('id')->on('pages')->onDelete('cascade');
            $table->index('depth');
        });

The code of the test is the following:

// ...
        list($page, $child1/*, $child2, $child3, $child4 */) = $this->prepareTestedSiblings();

        $children = Page::find(range(3, 5));

        foreach ($children as $child)
        {
            $child->moveTo($child1);
        }

        $this->assertEquals(3, $child1->countChildren());
        $this->assertEquals($child1->id, $children[1]->parent()->id);
// ...

The test reports Ok.

from closuretable.

@franzose How do I run that test? I'm not familiar with testing tests from installed packages.

from closuretable.

@ben-joostens, open terminal, then cd to vendor/franzose/ClosureTable/tests and run phpunit

from closuretable.

@ben-joostens, if you get an autoload.php failure when you run phpunit, perform a composer update in the same folder before you run phpunit

from closuretable.

Ok, here's the output, forgive me if I'm overlooking something simple, I haven't had the time to check out the tests just yet. But wanted to share it anyway.

http://paste.laravel.com/VXn

from closuretable.

@ben-joostens I'm working to fix the errors, anyway there are no failures in the tests, only sql exceptions that i'll correct

from closuretable.

@vjandrea Yep that's what I thought. I'll try and replicate the issue I was encountering with a test.

Btw, on those long loading times, can you tell me if that is to be expected?

from closuretable.

@ben-joostens, can you tell me the specs of the machine you're using? That's really not what's expected, normally queries using closure tables are pretty fast.

from closuretable.

@vjandrea specs won't be an issue, it's running on a dual xeon (Intel(R) Xeon(R) CPU E5620 @ 2.40GHz) with 16 cores total and 12 gigs of ram with an SSD for storage.

Do you mind showing me an export of a working dataset so I can compare? Or would you rather see a new export now that I was able to build the tree without errors?

from closuretable.

This is what happens when I fetch the 15 descendants of a parent:

http://paste.laravel.com/VZv

from closuretable.

With 33 nested items divided in two root trees i have a loading time of 3.93 seconds for a full rendered page on my local machine (macbook pro), here's the dump http://paste.laravel.com/W3z

from closuretable.

@vjandrea, slow actually...

from closuretable.

This is what happens when I fetch the 15 descendants of a parent

@ben-joostens, these weird queries are workaround to set ancestor, descendant and depth directly as the attributes to the Entity model... I'm trying to find a better solution for that.

from closuretable.