internal > Feature

refactor Filters / #1986

MoveBookmark

Summary

v0.093 - refactor query filters
open
Sep 13, 2006
10 hours ... 3 days
Sep 14, 2006 / pixtur
Jun 21, 2007 / xl
View previous 15 versions
 

Attached files

No files uploaded
Just some rough ideas on a long run refactoring...

Some notes about handling fields:

The primary purpose of the field-concept should be easy querying of database fields via filters.

There are some scenarios:

1. Custom flags for building the SQL-query:

  • visiblity

2. string-matching

  • for this we need to know the full text indexes

3. normal fields

  • Those fields make up most of the queries:
  • for numerical:
    • IS
    • IS NOT
    • BETWEEN
    • >
    • <
    • >=
    • <=
    • IN
    • NOT IN

Some examples for filters:
 new  FilterIs('project',2),
 new  FilterBetween('status',2,5),
 new  FilterMin('project',2),
 new  FilterMax('project',10),
 new  FilterIn('project',10),
 new  FilterNotIn('project',10),

maybe we should use the Field-Class instead:

 new FilterIs($my_task->fields['project'], 2)

or

 new $project->fields['project'].FilterIs(2)

or

 new FilterIs('task.project',2)

or

 new Task::FilterIs('project',2)

or

 Task::Filter('project','is',2)



  • for implementing those generic queries, distigusishing task and item will be really tricky. 
Task::query(array(
 new  FilterIs('project',2),
 new  FilterBetween('status',2,5),
 new  FilterMin('resovled',2),
 new  FilterMax('status',10),
 new  FilterIn('assigned_to',array($user1->id, $user2->id)),
 new  FilterNotIn('for_milestone', array(23,323)),
));

From Filter to query

simple

new  FilterMax('status',10)

This would be something like
AND task.status <= intval(10)

The task could be gathered from the field definition in class_task.inc.php

Or

new FilterIs('status',10)
 new FilterIs('status',11)

would be...

 AND task.status IN (10,11)

or...

 AND (task.status = 10 or task.status = 11)

For this we would need to build a hash with already defined filters and convert those into OR statements...

String Matching

new FilterSearch('company.name', 'IBN');

 AND (
    MATCH (company.name) AGAINST ('".asCleanString($search). "*') 
    OR  
    MATCH (company.comments) AGAINST ('".asCleanString($search)."*'  IN BOOLEAN MODE)
 )";

By automatically building the SQL-Code for string matching we also can take care of short words (e.g. "fmx") and common words (e.g. "road", "that") which are not covered by SQL fulltext indexes and cannot be searched today.





Assigment

new FilterAssignedTo($user->id);

would be

  SELECT i.*, t.* from {$prefix}item i, {$prefix}task t, {$prefix}taskperson tp, {$prefix}projectperson upp, {$prefix}item itp
  WHERE

  ...
  
  AND i.id = tp.task
     AND tp.person = $assigned_to_person
     AND itp.id = tp.id
     AND itp.state = 1

Note that the temp names tp and itp are only required in this mode.

Tricky queries

Some queries are extra tricky. The projects has to be limited for the task, as well as for the projectperson-row that it defining the user rights:

    $str_project= $project
        ? 'AND upp.project='.intval($project)
        : '';
    $str_project2= $project
        ? 'AND i.project='.intval($project)
        : '';


                "SELECT i.*, t.* from {$prefix}item i, {$prefix}task t, {$prefix}taskperson tp, {$prefix}projectperson upp, {$prefix}item itp
                WHERE
                        upp.person = {$auth->cur_user->id}
                    $str_project
                    AND i.type = '".ITEM_TASK."'
                    AND i.project=upp.project
                    $str_is_alive
                    $str_project2


also read:

4 Comments

pixtur

Oct 18, 2006
Update static $_type is no good...
This changes to db_item did not work out:

class DbItem{
 public static $_type;
...
class DbProjectItem extends DbItem
{

    public $fields_project;
    private $_values_org=array();
    public $children= array();

    /**
    * create empty project-item or querry database
    */
    public function __construct($id_or_array=NULL)
    {

        /**
        *  this->_type holds a string for the current type
        *  which is used for accessing db-tables and
        *  form-parameter-passing (therefore it has to be lowercase)
        */
        self::$_type=strtolower(get_class($this));
...


/*
    static function Filter($fwhat, $ftype, $fvalue)
    {
        if(!isset(self::$fields_static[$fwhat])){
            trigger_error("unknown field '$fwhat'");
        }
        $field= self::$fields_static[$fwhat];

        echo "type=". self::$_type;
        switch($ftype) {
            case 'is':
                return new FilterIs($field, $value, self::$_type);

            default:
                trigger_error("Unknown filter '$ftype'");
        }
    }

    static function queryPlus($filters)
    {

    }
*/

}

/*
class Filter
{
    public $type= '';
    public $value='';
    public $field='';
    public $tables= array();;

    public function getTables()
    {
        return $this->tables;
    }
}


class FilterIs extends Filter2
{
    public function __construct($field, $value, $type)
    {
        echo "%% filter is $field->name is $value, type= $type<br>";
        $this->type= $type;
        $this->field= $field;
        $this->value= $value;
        $this->tables[$type]= $type;
    }

    public function getWhere()
    {
        return "";
    }
}
*/

What I need:
  • static function DbItem::query() should be a generic function to query projectItems with filters. But when this function is been used in derived classes (e.g. Comment) the static member is of no use.
Problems:
  • static vars cannot be derived
Solution:
  • remove all occurences of $_type with function getType(): 
function getTypeName() {
        self::$_type=strtolower(get_class($this));
}

xl

Jun 1, 2007
sound fine
but i think it would be more modular to sy something like
new  Filter('project','=',2),
wouldnt it?

pixtur

Jun 21, 2007
version 2
on syntax
doing a lot of rails stuff recently... I am current with this syntax:

		$items = ItemPerson::find(array(
		    'conditions'=>array(		        
		        array('person','=',$auth->cur_user->id),
		        array('item','=',$project->id),
		        array('project','IN',array($my_projects)),
		    )
		    'order by' => 'task.name DESC'
		));

xl

Jun 21, 2007
version 2
query syntax
this looks intuitive and arrays are lightweight.
but how would i write something like filter "unassigned tasks" (for now)?
 
 

Comment / Update