complex insert/update operations on editable grid

HI,

I am designing a page which is having editable grid containing checkboxes(property_id). These checkboxes will be either checked or unchecked based on existing data. If I make any changes to checkbox (either checked or unchecked)
status(is_active: boolean) should changes in database. My database table structure is following:

CREATE TABLE `user_property_info` (
`user_property_info_id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Note 1: user_property_info_id is a auto increment primary key where as user_id & property_id are foreign keys

Note 2: If there are 3 properties (lets say 100, 101, 102) which are associate with user_id (222) than on page load pre-filled rule input looks like. Based on this rule input grid selections happens.

{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: true
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
}

Case 1: If I checked new property_id (let say 109) then the above rule input should be like this (New record appends and user_property_info_id will be null)
{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: true
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
{
user_property_info_id : null,
user_id : 222,
property_id: 103,
is_active: true
},
}

Case 2: If I unchecked existing property_id (let say 101) then the above rule input should be like this (is_active will set to false. Please see second record having property id 101)
{
{
user_property_info_id : 1,
user_id : 222,
property_id: 100,
is_active: true
},
{
user_property_info_id : 2,
user_id : 222,
property_id: 101,
is_active: false
},
{
user_property_info_id : 3,
user_id : 222,
property_id: 103,
is_active: true
},
{
user_property_info_id : null,
user_id : 222,
property_id: 103,
is_active: true
},
}


Case 3: Any new property which will be added and then removed (on same process) will not impact on rule input.


Once we get proper rule input we will pass these for further database insert / update operations. Here if user_property_info_id is null it is treated as insert operation else act as a update operations.


Thanks!

  Discussion posts and replies are publicly visible

Parents Reply Children
No Data