'ZfcRbac\Provider\NestedSet\Lazy\DoctrineDbal' load api sql problem (incompatibility with the schema)

[hyoussef]

Hi, I am trying to use the Lazy provider 'ZfcRbac\Provider\NestedSet\Lazy\DoctrineDbal'. the load api seems to be based on other schema specification (check generated sql statement, used column names ...)

example(the role column name is role_name but in the api trying to use name ...). So as result, the api is not working at all with sql exceptions.

Could some one have a look or did I missed something? Thanks ( version : "dev-master" )

[hyoussef]

seems to be in relation with #23

[bakura10]

I'm not using this so I cannot really help, sorry. Maybe my dear @manuakasam ?

[bakura10]

Note: I've checked the code, I remember that I made some research about it. It's not easy to understand but there are a lot of resources (like this : http://www.evanpetersen.com/item/nested-sets.html)

Use a piece of paper, it really helps. Once you understand the algorithm it's pretty simple to make a schema.

[hyoussef]

thanks for the link. I was checking this one http://www.fliquidstudios.com/2008/12/23/nested-set-in-mysql/ The problem is how to maintain such structure when you update records!

[bakura10]

From what I remember the harder is not update records (it's just a few additions), but rather move nodes. This is a complete hell.

[hyoussef]

Is there any other solution to have lazy loading of roles/permission based on the adjacentList (the simple schema!)

[bakura10]

I don't know sorry :/. Let's wait for @spiffyjr on that :).

[manuakasam]

Just a quick throwing in: the provided schemata indeed is for the adjancy list. Am i correct in understnding that the main problem is to get the correct schemata, or is there any other problem? Haven't worked with NS in a while but i probably can get the schema working (though i cant test as i dont use ZfcRbac yet)

From what i can tell it should be this:

CREATE TABLE `role` (
    `id`   INT(11)     UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `lft`  INT(11)     UNSIGNED NOT NULL,
    `rgt`  INT(11)     UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `lft` (`lft`),
    KEY `rgt` (`rgt`)
);

CREATE TABLE `role_permission` (
    `role_id` INT(11) UNSIGNED NOT NULL,
    `perm_id` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`role_id`, `perm_id`),
    KEY `perm_id` (`perm_id`),
    KEY `role_id` (`role_id`)
)

CREATE TABLE `permission` (
    `id` int(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
)

But then again, i do not really understand these lines: https://github.com/ZF-Commons/ZfcRbac/blob/master/src/ZfcRbac/Provider/NestedSet/Lazy/DoctrineDbal.php#L131-L135 ^-- If that is another self-join on the table role then i am just way too confused with the schema

[hyoussef]

Thanks @manuakasam I will give it a try.

[spiffyjr]

You have to use a nested set for lazy loading because of the way the queries work to find the roles you need. The schema above looks correct.

[hyoussef]

Yes, it is working now.