global filter or mega view of database?

[cookie720]

Hello,
Is there any way I can create a global filter for my database (a couple drop downs somewhere near the search box), that when its set to something, the tabs in the application only show tables and records that are related by that certain filter? In essence customising my whole databases tables to show records only i want to see.

For example:
I have a central table, orders, and 9 other tables, that are all linked to it, some of which include users and clients

The filters I would like to have are: show all orders, where its filtered by a certain clients last name.
Filter 2: order numer
Filter 3: certain user linked to an order (salesperson who put the order through)

Each of three filters can be set, so you can combine all 3 to get a complex query

Maybe since all 9 tables are linked to the central one, create a mega-view, which pulls data from each table, and create the three filter criteria there?
How do you create views in xataface?

Any help would be greatly appreciated.
Thanks in advance

[shannah]

Here is one strategy.

1. Add a drop-down menu for users to select the filter that you want. Have the onchange handler for this dropdown to call an action that saves this value in session vars.

2. Add a security filter for each table that you want to be filtered in response to the user's selected filter.

The security filter would probably be implemented in the init() method of each table's delegate class.. something like:

Code: Select all

function init(Dataface_Table $table){
    if ( @$_SESSION['current_category'] ){
        $table->setSecurityFilter(array('category' => '='.$_SESSION['current_category']));
    }
}


(This assumes that you are storing the filter in the current_category session var, and using it to filter on the table's 'category' field.)

-Steve

[cookie720]

Thankyou or the reply,

I'm not too good with PHP or sessions so I will have some questions,

I have a drop down menu in the top bar, however it doesn't have any dynamic way to populate the list. Adding in each value manually, it filters the table the same way as using "filter=1" in fields.ini, But when i want to filter it back to "All", I have to refresh the page,

Code: Select all

<div style="float:left;">
         <div style="clear:both">
            <div class="resultlist-filters">
               <h3>Filter Results:</h3>
               <ul>
                  <li>
                     ResponsibleSolicitor <select onchange="resultlist__updateFilters('ResponsibleSolicitor', this);">
                        <option value="">All</option>
                        <option value="user1">user1</option>
                        <option value="user2">user2</option>
                        <option value="user3">user3</option>
                     </select>
                  </li>
               </ul>
            </div>
         </div>
      </div>


Also, where should I put <?php session_start(); ?>

Also, how can I make this "global filter" actually filter the tables that are related to the main table.

For example: There are 3 tables" Order, User, Product. User and Product are linked to Order. Are we able to lookup products bought by a user? Scenario: We are in the Order tab and the global filter is set to user1, We can see all orders by user1, but if I switch to the Product tab, will I see all products that the user is linked to?

Many thanks in advance!

[shannah]

Here are some snippets from an application that uses filters similar to what your app must do. The idea with this app is that each user can be a member of one or more companies. And the application can switch between the context of any of the companies in the system. There are security filters on all tables to only show rows that pertain to the current company.

First part:

I created a function to determine the current companyID:

Code: Select all

function getCompanyID(){
   static $company_id = -1;
   if ( $company_id == -1 ){
      $user = getUser();
      if ( !$user ) return null;
      
      $company_id = @$_GET['--company-id'];
      if ( !$company_id ){
         $company_id = $_SESSION['--company-id'];
      }
      if ( !$company_id ){
         $res = db_query("select company_id from user_companies where user_id='".$user->val('user_id')."' limit 1");
         if ( mysql_num_rows($res) > 0 ){
            list($company_id) = mysql_fetch_row($res);
         }
         @mysql_free_result($res);
      }
      
      if ( !$company_id ){
         $res = db_query("select company_id from companies limit 1");
         if ( mysql_num_rows($res) > 0 ){
            list($company_id) = mysql_fetch_row($res);
         }
         @mysql_free_result($res);
      }
      
      if ( $company_id ){
         $_SESSION['--company-id'] = $company_id;
      }
   }
   return $company_id;
   
}


Basically, this allows you to change the current company ID at any time by passing the --company-id GET parameter. It stores this in a session variable. If no parameter is set, it just gets the existing company ID from the session.

I place this function in some external PHP file that is included at the beginning of the index.php file. It just needs to be accessible anywhere I need it.

Next, in the Application delegate class, I add a block definition to add the dropdown list to select the current company:

Code: Select all

function block__before_header(){
   
      $res = db_query("select company_id, company_name from companies order by company_name");
      
      echo '<div style="height: 30px; text-align:right"><select onchange="window.location.href=this.options[this.selectedIndex].value;">';
      $url = Dataface_Application::getInstance()->url('');
      $url = preg_replace('/&company_id=[^&]*/','', $url);
      while ($row = mysql_fetch_assoc($res) ){
         $selected = '';
         if ( $row['company_id'] == getCompanyID() ){
            $selected = ' selected';
         }
         echo '<option value="'.htmlspecialchars($url.'&--company-id='.$row['company_id']).'"'.$selected.'>'.htmlspecialchars($row['company_name']).'</option>';
      }
      echo '</select></div>';
      
   }


Now that we have the ability to track the current company ID, we can do a filter. Note that in this particular application, all of the tables contain a company_id field so that each record is associated with one particular company. This convention made is easy to filter any table based on the company_id field.

Finally, and also in the Application Delegate class, I implement the beforeHandleRequest() method to add the current company id as one of the search parameters in all tables except the companies table:

Code: Select all

function beforeHandleRequest(){
   
      $app = Dataface_Application::getInstance();
      $query =& $app->getQuery();
      $table = Dataface_Table::loadTable($query['-table']);
      if ( !$_POST and !@$query['company_id'] and $query['-table'] != 'companies'){
         if ( $table->hasField('company_id') ){
            $query['company_id'] = getCompanyID();
         }
      }
      
      //print_r($query);
   
   }


We went a step further, for data entry, and made it so that the company_id field would be automatically populated with the current company ID when records are inserted. We added the following beforeInsert() and company_id__default() methods to each table delegate class:

Code: Select all

<?php
class tables_xxx {
   function beforeInsert($record){
      if ( !$record->val('company_id') ){
         $record->setValue('company_id', getCompanyID());
      }
   }
   
   function company_id__default(){
      return getCompanyID();
   }
}


In this solution I don't actually use security filters. Instead I just manipulate the query in the beforeHandleRequest phase. This solution was used in this case because the filtering is not done for security - it is done for usability and flow control. Make sure you know the difference in your own application.

-Steve