Filter RecordSets based on another field

PHPMaker User Submitted Tips (PHPMaker)
1

An app I was working on needed special selection of records based on the users who were allowed to access the record – only users assigned to that client could see the records along with any sub-records.this sample will should the routines needed to select the records, you can change the field names to what you need. AS well, you may have a better way to get the data out of a json string, than that of what i was doing – as noted this was a POC the functions could probably be cleaned and fined tuned much betterin the clients table I added 2 fields - the primary user that can see the record eg (5), and another field with any additional users that could see the record eg: (4,6,44,675)TABLE: Clients
Fields:
internal_amc_assigned - INT
internal_additional_amc_assigned - VARCHAR()in the Clients Record_selecting event:

// this function just filters out the clients that the current user is allowed to see
//
// Recordset Selecting event
function Recordset_Selecting(&$filter)
{
    global $Security;

  if(!$Security->isAdmin()) {
	$filter .= CurrentUserID(). " IN (internal_amc_assigned)";
   }
}

in the Server GLOBAL Code

// This function will retrieve all the clients that the user is allowed to access, this is used by practically all the other routines
//
   function _getAllowedClients($userid) {
        $objects = "";
        
        // primaray AMC
        $sql = "SELECT id FROM clients c WHERE '".$userid."' IN (c.internal_amc_assigned)";
        $rs = ExecuteJson($sql);
        
       // strip out all the json characters - find a better way
        if($rs) {
            $objects = str_replace("[", "", $rs);    // remove [
            $objects = str_replace("]", "", $objects);    // remove ]            
            $objects = str_replace("{", "", $objects);    // remove {
            $objects = str_replace("}", "", $objects);    // remove }            
            $objects = str_replace("\"", "", $objects);    // remove }]            
            $objects = str_replace("id:", "", $objects);    // remove }]            
        }

        // additional AMC's who have access to the records
        $sql1 = "SELECT id FROM acctrack.clients c WHERE FIND_IN_SET ($userid, c.internal_additional_amc_assigned)";
        $rs1 = ExecuteJson($sql1);
        
       // strip out all the json characters - find a better way
        if($rs1) {
            $objects1 = str_replace("[", "", $rs1);    // remove [
            $objects1 = str_replace("]", "", $objects1);    // remove ]            
            $objects1 = str_replace("{", "", $objects1);    // remove {
            $objects1 = str_replace("}", "", $objects1);    // remove }            
            $objects1 = str_replace("\"", "", $objects1);    // remove }]            
            $objects1 = str_replace("id:", "", $objects1);    // remove }]            
        }
        if(strlen($objects) > 0 AND strlen($objects1) > 0)
            $objects .= ",";
        
        $objects .= $objects1;
        
        return ($objects);
    }

now, to get corresponding child records for a client record, you will need to create specific functions to retrieve that information for each sub-table, this exampe will show using 1 sub-table client_employees. As well, all the sub-tables will need a field that links it back to the master table…TABLE: Client_Employees (holds employee records for each client)add the following to the client_employees record_selecting event:

function Recordset_Selecting(&$filter)
{
    global $Security;
    if(!$Security->isAdmin()) {
        $objects = _getAllowedClients(CurrentUserID());

        if($filter != "") {
                    if($objects != "") $filter .= " AND EmployeeEmployerId IN ($objects)";
                }
        else {
            if($objects != "") 
            $filter .= " EmployeeEmployerId IN ($objects)";	
        }
    }
}

what’s happening… when you display the client_employees table, the routine will get all the clients the user is allowed to access, then simply filters the client_employees table using the EmployeeEmployerId (which holds the client id) with that of the client id’s in the $objects string.
for example if User 1 can see client id 2, 3, the above should list all the employee records for both client 2 and 3.you can use the above concept for simple master-detail scenarios.but now what if your client_employees has detail records, this is where you now would need to create a specific function to retrieve the records for each detail table.This sample shows how to filter records based on a detail table. For each employee – they can have many documents attached to them

CLIENTS
|_____ EMPLOYEES
                |____________CLIENT_EMPLOYEES_DOCS

TABLE: CLIENT_EMPLOYEES_DOCSin the client_employees_docs recordset_selectinng event:

// Recordset Selecting event
function Recordset_Selecting(&$filter)
{
        global $Security;
        
         if($this->PageID == "list") {
                if(!$Security->isAdmin()) {
                    $objects = _getAllowedClientsEmployees(CurrentUserID());
                  
                  if($filter != "") {
                    if($objects != "") $filter .= " AND EmployeeId IN ($objects)";
                  }
                  else {
                    if($objects != "") 
                        $filter .= " EmployeeId IN ($objects)";    
                    else
                    	$filter .= "0=1";
                  }                        
                }                    
        }
}

notice the new function needed _getAllowedClientsEmployees(), Add the new function to your global code:

    function _getAllowedClientsEmployees($userid) {
        $objects = "";

        $clients = _getAllowedClients($userid);
        
        $sql = "(SELECT distinct e.id FROM clients_employees e WHERE e.EmployeeEmployerId IN ($clients))";        
        
        $rs = ExecuteJson($sql);
        if($rs) {
            $objects = str_replace("[", "", $rs);    // remove [
            $objects = str_replace("]", "", $objects);    // remove ]            
            $objects = str_replace("{", "", $objects);    // remove {
            $objects = str_replace("}", "", $objects);    // remove }            
            $objects = str_replace("\"", "", $objects);    // remove }]            
            $objects = str_replace("null", "0", $objects);    // remove null            
            $objects = str_replace("id:", "", $objects);    // remove }]
        
           if(strcmp(",", $objects) == 0)   
                $objects = "";
        }
        return ($objects);
    }

what the above does is basically gets the clients the user can see, then gets all the employees the user can see, that object list is returned to the recordset_selecting event and then only the documents for the employees in that object list are displayed.if the “master” table has many sub-tables, in my case the employee had documents, notes, cases I would need 3 global functions to retrieve the objects for each of those table, which are based identical to above, just the sql query is different. in my case i had_getAllowedClientsEmployees() “master”
_getAllowedClientsEmployeesDocs() “detail”
_getAllowedClientsEmployeesNotes() “detail”
_getAllowedClientsEmployeesCases() "detail"here’s the function to get the notes:

    function _getAllowedClientsEmployeesNotes($userid) {
        $objects = "";

        $clients = _getAllowedClients($userid);
        
        $sql = "SELECT distinct EmployeeId FROM clients_employees_notes n WHERE n.EmployeeId IN ($clients)";
        
        $rs = ExecuteJson($sql);
        if($rs) {
            $objects = str_replace("[", "", $rs);    // remove [
            $objects = str_replace("]", "", $objects);    // remove ]            
            $objects = str_replace("{", "", $objects);    // remove {
            $objects = str_replace("}", "", $objects);    // remove }            
            $objects = str_replace("\"", "", $objects);    // remove }]            
            $objects = str_replace("null", "0", $objects);    // remove null            
            $objects = str_replace("EmployeeId:", "", $objects);    // remove }]
            if(strcmp(",", $objects) == 0)   
                $objects = "";
        }
        return ($objects);
    }

and similar, the cases

    function _getAllowedClientsEmployeesCases($userid) {
        $objects = "";

        $clients = _getAllowedClientsEmployees($userid);
        
        $sql = "SELECT distinct EmployeeId FROM clients_employees_cases c WHERE c.EmployeeId IN ($clients)";
        
        $rs = ExecuteJson($sql);
        if($rs) {
            $objects = str_replace("[", "", $rs);    // remove [
            $objects = str_replace("]", "", $objects);    // remove ]            
            $objects = str_replace("{", "", $objects);    // remove {
            $objects = str_replace("}", "", $objects);    // remove }            
            $objects = str_replace("\"", "", $objects);    // remove }]            
            $objects = str_replace("null", "0", $objects);    // remove null            
            $objects = str_replace("EmployeeId:", "", $objects);    // remove }]
            if(strcmp(",", $objects) == 0)   
                $objects = "";
        }
        return ($objects);
    }

then you just tweak each recordset_selecting even to filter for each tablehere’s the client_employees_notes recordset_selecting event:

// Recordset Selecting event
function Recordset_Selecting(&$filter)
{
	global $Security;
	if(!$Security->isAdmin()) {
	    $objects = _getAllowedClientsEmployeesNotes(CurrentUserID());
                  
	if($filter != "") {
    	if($objects != "") $filter .= " AND EmployeeId IN ($objects)";
    }
    else {
        	if($objects != "") 
                $filter .= " EmployeeId IN ($objects)";	
            else
                $filter .= "0=1";
    }						
       }
 }

here’s the event for the employee_cases table

// Recordset Selecting event
function Recordset_Selecting(&$filter)
{
	global $Security;
	if(!$Security->isAdmin()) {
	     $objects = _getAllowedClientsEmployeesCases(CurrentUserID());
                  
	if($filter != "") {
    	if($objects != "") $filter .= " AND EmployeeId IN ($objects)";
    }
    else {
    	if($objects != "") $filter .= " EmployeeId IN ($objects)";	
    }				
       }
 }

notice they are all the same except for the function call.this was a proof of concept app, but was scrapped due to costs. the above function concepts seemed to work as far as I had tested, and was responsive, i saw no lag when the functions executed, records were filtered accordingly, but who knows…

as far as CRUD security goes, you would need to give the user the appropriate perms to view/edit/add etc, regardless of the above functionsas the above has nothing to do with owner id, if the current user isn’t the owner, then the standard perms for edit/del will be applied, if you need the current user to be able to edit someone else’s record, you will need to override or apply the appropriate perms to the user in the other events.hope this gives you some insight on one way to filter records based on other fields.just a note from experience of this type of filtering, this “security” method… is a maintenance nightmare, the system we were using was customized to filter as noted above, but after a while, employee turnover, last minute adds, users missing from the records, it was scrapped – just too much time was needed to keep it updated.

2

Thanks for this.
I took a few tips from this post to solve a filtering challenge.
In addition to the standard userlevels permissions system, a while back I added a secondary groups layer to my project. So any user can be assigned to any number of groups, and based on their group memberships recordsests can be filtered and other various rules applied. It’s so much more flexible.For my feedback module I needed to filter based on userlevel, group membership, whether the user had added the record or not, whether the user was the target of the feedback, or whether the feedback had been marked as confidential or not. Posting it here in case anyone finds it useful.Feel free to offer improvements:

function Recordset_Selecting(&$filter)
{
    $CurrentUserLevel = CurrentUserLevel();
    $currentUserID = CurrentUserID();

	$bypassed = false;
    if (IsCurriculumMananger($currentUserID) || IsPersonnelManager($currentUserID) || IsManagement($currentUserID)){  //if user is member of these groups, bypass the filter
        $bypassed = true;
    }

    if (!$bypassed) {
        $getrecordsReg = '';
        $getrecordsAdmin = '';
        $getrecordsReg = ExecuteRows("SELECT id FROM Staff_Feedback WHERE AddedBy='$currentUserID' OR (fk_StaffID='$currentUserID' AND Confidential='0')");  //get ids of records regular user is allowed to see
        $getrecordsAdmin = ExecuteRows("SELECT id FROM Staff_Feedback WHERE Confidential='0' OR (AddedBy='$currentUserID' AND Confidential='1')");  //get ids of records admin user is allowed to see
        $recordIDsReg = implode(',', array_column($getrecordsReg, 'id'));
		$recordIDsAdmin = implode(',', array_column($getrecordsAdmin, 'id'));
        // Regular user
        if ($CurrentUserLevel != '10' && $CurrentUserLevel != '-1') { 
            AddFilter($filter, "id IN ($recordIDsReg)");
        } else {
            // Admin user
            AddFilter($filter, "id IN ($recordIDsAdmin)");
        }
    }
}