security filters in table

[samhans]

hai all,
i have set security filters in one of my table. so that owner of the field can see there record only.
now i have also a field called as approver, what i want that in the same table i can also.set the security filters such that when approver.logs in he could see the record where his userid is set as the approver .

thanks in advance

[shannah]

The solution would depend on your database structure. If their status of an approver is not stored in that table (e.g. if you have a many-to-many relationship that dictates who is an approver for a record, then you would need to find a way to graft a status field onto the table. I'll often use the __sql__() delegate class method to graft a field onto the table that depends on the logged in user. e.g.

Code: Select all

class tables_companies {
    function __sql__(){
        $user = getUser();
        $person_id = 0;
        if ( $user ) $person_id = $user->val(’person_id’);
        return "select c.*, pc.company_role my_company_role from
                companies c left join people_companies pc on (c.company_id=pc.company_id and pc.person_id='".addslashes($person_id)."')";
    }
}


You must take care that the __sql__() method returns a result set with the same rows as the default set.

-Steve

[samhans]

Steve,
i have that approver column in the same table . the problem is i have set the owner id security filters in this table, so when approver log in with his id , he doesn't, able to see any record.

so is there is any way i could set two security filters in one table. so that owner id and approver userid could be see there own record .

[shannah]

So you need an "or" find clause. Xataface doesn't do "or" on multiple fields. But there is a workaround similar to the one I posted above. Create a grafted field on the table that describes the user role for a record. Eg it would include an SQL if statement so that the value would be owner if the current user is the owner and approved if the current user is the approver and null otherwise. You can the search on that field or make a security filter on it.

Steve

[samhans]

steve ,
can you explain me with some examples. i have doubt in configuring grafted filed.


thanks for your help

[shannah]

Suppose you have 2 columns: owner_id and approver_id. Then you want to create a grafted field called role such that:
If current user is the owner, role = 'owner'
else if current user is an approver, role = 'approver'
else role is null.

Then you could implement an __sql__() method like:

Code: Select all

function __sql__(){
    $user_id = getCurrentUserId(); ///
    return "select t.*, if(owner_id='".addslashes($user_id)."','owner', if(approver_id='".addslashes($user_id)."', 'approver', null)) as role from mytable t";
}


Then you could set a security filter on the "role" field:

Code: Select all

$table->setSecurityFilter(array('role'=>'>'));


-Steve

[samhans]

thanks Steve for your great example. i am working on it.

one more question related to it. in the same table i have a status column. what i want that when the demand was fulfilled and store incharge has changed the status to closed. it should be removed from the list view.

how can i do this because already i have used the security filter option and i cannot redeclare it in same delegate class.