Set is broken but fixTree return null

[hiendv]

I have a nested set which contains about 5200 nodes. The isBroken() it returns true but fixTree() always returns null. Is there any problem with a large set?

{
    "name": "kalnoy/nestedset",
    "version": "v4.0.1"
}

[lazychaser]

Please provide an output of countErrors() function.

[hiendv]

Sorry for the late response. Here's my result from tinker

Psy Shell v0.6.1 (PHP 7.0.1-1+deb.sury.org~trusty+2 — cli) by Justin Hileman
>>> FooBar::isBroken()
=> true
>>> FooBar:: countErrors()
=> [
     "oddness" => 5,
     "duplicates" => 20,
     "wrong_parent" => 0,
   ]
>>> FooBar::fixTree()
=> null

[lazychaser]

Are you using soft deletes?

[hiendv]

No, I'm not. Could you explain oddness and duplicates?

[lazychaser]

oddness means there are nodes that have invalid _lft/_rgt values (lft > rgt or (rgt - lft) is divided by two)
duplicates means that some nodes have same _lft/_rgt values

Run this query: select * from foo_bar where _lft >= _rgt or (_rgt - _lft) % 2 = 0 and look what those nodes have in common

[hiendv]

There are 5 rows. They share the same parent_id, _lft and _rgt are both 0

[lazychaser]

I guess the only reason of this issue is that those nodes have parent_id pointing to a node that doesn't exists. This fixing algorithm is not aware of this case.

[hiendv]

Well, they are, indeed. I should have checked the parent sooner, sorry. Thank you so much !

[lazychaser]

I think those nodes should be saved as roots when fixing the tree. Also, setting up a foreign key would be nice

[hiendv]

@lazychaser In my case, the parent_id is optional. In the first place, I thought using NULL in foreign keys is a little bit tricky but I was wrong, a parent_id mutator is fine ! Thank you ;)

[hiendv]

@lazychaser Sorry, I know this is OT but I'm unable to add the foreign key to the current schema of the set. Any advices?

alter table `foo_bar` add constraint foobar_parent_id_foreign foreign key (`parent_id`) references `foo_bar` (`id`) on delete cascade;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`#sql-4e0_4992`, CONSTRAINT `foobar_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `foo_bar` (`id`) ON DELETE CASCADE)

[lazychaser]

This means that there are still nodes that have invalid parent_id value. This might help:

select * from foo_bar where parent_id is not null and not exists (select 1 from foo_bar p where p.id = foo_bar.parent_id)

This query will display nodes pointing to invalid parents.

[hiendv]

@lazychaser Thank you so much. It's clear now !

[lazychaser]

Great, glad to help