⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

set_relation_n_n not updating realtion table



Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 05 June 2014 - 04:50 AM

I want to use 1-n relation between tables.

 

I have three tables USER, PERMISSION and UserPermission as below

 

USER(
  Id INT auto_increment,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Description VARCHAR(255),
  Gender VARCHAR(6) NOT NULL,
  Email VARCHAR(255) NOT NULL,
  Address VARCHAR(255),
  Username VARCHAR(20) NOT NULL,
  Password VARCHAR(255) NOT NULL,
  UNIQUE(Username),
  UNIQUE(Email),
  PRIMARY KEY(Id)
)
PERMISSION(
  Id INT auto_increment,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id),
  UNIQUE(Name)
)
UserPermission(
  Priority INT auto_increment,
  UserId INT NOT NULL,
  Permission VARCHAR(50) NOT NULL,
  PRIMARY KEY(Priority),
  FOREIGN KEY(UserId) REFERENCES USER(Id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(Permission) REFERENCES PERMISSION(Name) ON DELETE CASCADE ON UPDATE CASCADE
)

I'm trying to add new user with following controller code:

$crud = new grocery_CRUD();
$crud->set_table('user');
$crud->set_subject('User');

$crud->columns('FirstName','LastName','Gender','Username');
$crud->fields('FirstName','LastName','Description','Gender','Email','Address','Permissions','Username','Password','ConfPassword');
$crud->display_as('FirstName','First Name');
$crud->display_as('LastName','Surname');
$crud->display_as('ConfPassword','Confirm Password');

$crud->field_type('Description','text');
$crud->field_type('Gender', 'enum',array('Male','Female'));
$crud->field_type("Address", "text");
$crud->field_type("Password", "password");
$crud->field_type("ConfPassword", "password");

if ($crud->getState() == 'read') {
  $crud->field_type('Password', 'hidden');
  $crud->field_type('ConfPassword', 'hidden');
}
$crud->set_relation_n_n('Permissions', 'userpermission', 'permission', 'userid', 'permission', 'name','priority');

/*required field in add/edit*/
$crud->required_fields('FirstName','LastName','Gender','Email','Username','Password','ConfPassword');
$crud->unique_fields('Username','Email');

$crud->callback_before_insert(array($this,'unset_verification'));
$crud->callback_before_update(array($this,'unset_verification'));

$output = $crud->render();
$this->load->view('user/view/user',$output);

When I add new user it asks to select multiple permissions, but when I save it, the permissions data is not getting reflected in any of the tables mentioned above.

 

UserPermission table is blank. Can anybody help me to know what going wrong in this code??

 

Do i need to have Permissions field in USER table? If yes, what it's type should be?

 

And one more question, what is the priority field? (I just borrowed it from example given in GC). Is it needed? If yes, for what? Can I use Id instead of priority?


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 05 June 2014 - 11:00 AM

I changed table definitions as below:

 

UserPermission(
  Id INT auto_increment,
  UserId INT NOT NULL,
  PermissionId INT NOT NULL,
  PRIMARY KEY(Id),
  FOREIGN KEY(UserId) REFERENCES USER(Id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(PermissionId) REFERENCES PERMISSION(Id) ON DELETE CASCADE ON UPDATE CASCADE
)

and set_relation_n_n as below:

$crud->set_relation_n_n('Permissions', 'userpermission', 'permission', 'userid', 'permissionid', 'name','id');

Now data is getting stored in userpermission table, but few gets added and few not... e.g. If I add 4 permission, only 2 or 3 gets added into userpermission. Also while editing (updating), only one item is shown in selected list.


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 05 June 2014 - 12:45 PM

When I remove last parameter from set_relation_n_n, it's updating all selected items in table as desired. change is as follows:

$crud->set_relation_n_n('Permissions', 'userpermission', 'permission', 'userid', 'permissionid', 'name');

But still in read view, I see only one field being displayed in the 'permissions' field. And also in drop down list, it shows all items (including old selected items, which should all be shown in field value space and not in drop down, as being already selected.)

 

So, what change is needed further? And what's the purpose of priority (in my case it was 'id') as given in set_relation_n_n example at GC ( http://www.grocerycrud.com/documentation/options_functions/set_relation_n_n ) ??


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 05 June 2014 - 20:15 PM

well priority is a variable used when you have multi select with the order in which that gets selected.. there's still the feature (i believe) to shuffle your selection and in that scenario - you need the priority field.


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 12 June 2014 - 08:50 AM

Read view and edit view shows just one item (even though there are more that one items selected while adding)...

 

Also not all selected permissions are getting added into UserPermission table... (not getting why is this happening)...

 

> [member='web-johnny'] , hope you have answer for this...

 

MySQL table are defined as below:

 

USER(
  Id INT auto_increment,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Description VARCHAR(255),
  Gender VARCHAR(6) NOT NULL,
  Email VARCHAR(255) NOT NULL,
  Address VARCHAR(255),
  Username VARCHAR(20) NOT NULL,
  Password VARCHAR(255) NOT NULL,
  UNIQUE(Username),
  UNIQUE(Email),
  PRIMARY KEY(Id)
)

PERMISSION(
  Id INT auto_increment,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id),
  UNIQUE(Name)
)

UserPermission(
  Id INT auto_increment,
  UserId INT NOT NULL,
  PermissionId INT NOT NULL,
  PRIMARY KEY(Id),
  FOREIGN KEY(UserId) REFERENCES USER(Id),
  FOREIGN KEY(PermissionId) REFERENCES PERMISSION(Id)
)

and set_relation_n_n is used as below:

 

 

$crud->set_relation_n_n('Permissions', 'userpermission', 'permission', 'userid', 'permissionid', 'name');

Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 12 June 2014 - 18:03 PM

well.. do a debug .. let codeigniter spitout the profiler output while you read.. look @the query.. execute the same in the sql (the one with relation) and see the output first.. is it making through with 1 result only or multiple results?


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 13 June 2014 - 06:33 AM

Now I found that tables are getting updated correctly as per selected list.. but problem is with read/edit, in there it's showing only 1 item as old value(s), where it should show all previously selected items (permissions).. Why is not showing all previously selected items?

 

I had configured for $config['grocery_crud_dialog_forms'] = true; and so could not see error...

When I changed it to $config['grocery_crud_dialog_forms'] = false; I found on view/edit following error occured:

 

(Note: But still there was one item shown in Permissions field..)

A PHP Error was encountered
Severity: Notice
Message: Undefined property: stdClass::$permissionid
Filename: models/grocery_crud_model.php
Line Number: 343
Where, line 343 in grocery_crud_model.php  is:  
 
$results_array[$row->{$field_info->primary_key_alias_to_selection_table}] = $row->{$field_name_hash};
And when I changed following line ('permissionid' changed to 'id'), the error disappeared but now not even single item is displayed in corresponding field ('Permissions') on view/edit.
$crud->set_relation_n_n('Permissions', 'userpermission', 'permission', 'userid', 'id', 'name');

So, I'm doubtful about table definitions or field names used in set_relation_n_n...

 

Can you see any problem in above definitions??


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 13 June 2014 - 08:25 AM

no my friend u cannot expect the result using id ... it has to have permission id...

as i told u.. use CI profiler to generate the output while in edit / read mode.. you will understand the queries being executed.. its output and stuff..

Based on that we can proceed further solving this issue.. If the query output and stuff is fine with ... check the source code generated for the multi select.. if all the entities exists but with the errors / notice / warnings.. (ofcourse those should not be the case) then we can think of some solution other than that...

 

Also it will be great if you could have put up the app to public access and shared (if required .. to me in PM) thn we can look into the same and try and fix up the stuation,


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 13 June 2014 - 14:11 PM

On read, CI profiler shows nothing userful other than db queries executed..

Attached image showing corresponding details...

 
DATABASE:  company_24_db   QUERIES: 10  (Hide) 

0.0000   SHOW TABLES FROM `company_24_db` 
0.0030   SHOW COLUMNS FROM `user` 
0.0040   DESCRIBE `user`  
0.0030   SHOW COLUMNS FROM `user`  
0.0020   DESCRIBE `user`  
0.0000   SELECT *
FROM (`user`)
WHERE `Id` =  '1'  

0.0030   DESCRIBE `permission`  

0.0010   SELECT *, name as sb068931c
FROM (`userpermission`)
JOIN `permission` ON `userpermission`.`permissionid` = `permission`.`Id`
WHERE `userid` =  '1'
ORDER BY `permission`.`name`  

0.0030   SHOW COLUMNS FROM `user`  
0.0020   DESCRIBE `user` 

How to get query result too in profiler?

 

I'm not getting if something is wrong from queries...


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 13 June 2014 - 17:14 PM

no.. i aint saying anything will be wrong in queries... cuz they generated...

secondly u cant get result in profiler...

what you can do is.. pick the query and hit in the sql console... and see if it is getting the desired output or not!!!


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 14 June 2014 - 05:06 AM

yes, I'm getting desired output in sql console..

 

Query:

SELECT *, name as sb068931c
FROM (`userpermission`)
JOIN `permission` ON `userpermission`.`permissionid` = `permission`.`Id`
WHERE `userid` =  '1'
ORDER BY `permission`.`name`

Result:

Id UserId PermissionId Id       Name          sb068931c   
19 1            16     16 invoice_settings   invoice_settings 
20 1            6      6  stock_all          stock_all 
21 1            7      7  stock_create       stock_create 
22 1            10     10 stock_delete       stock_delete 
23 1            8      8  stock_read         stock_read 
24 1            9      9  stock_update       stock_update

But in view it's showing just one item ('stock_update'), which is the last one in above returned list...


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 14 June 2014 - 09:19 AM

can u push the code somewhere on live / public ip so atleast if required can view the output atleast!! and try and figure out where the isue / trouble is!


Nitin Thokare

Nitin Thokare
  • profile picture
  • Member

Posted 15 June 2014 - 16:00 PM

Hi.. thanks for ur help... can u plz give me your email address(possibly gmail) so that we can discuss in detail over there...

My email address is... ndthokare86@gmail.com