...
| Code Block | ||||
|---|---|---|---|---|
| ||||
private function fetch($request)
{
// get the DataSource
$ds = $this->data_source;
$tbl_name = $ds['ID'];
// check the advanced cretira
if(empty($request->advancedCriteria))
{
$query_result = $this->buildStandardCriteria($request);
} else
{
$query_result = $this->buildAdvancedCriteria($request);
}
$query = "select * from $tbl_name s ";
$query .= $query_result['query'];
// sort by
if( !empty($request->sortBy) )
{
// set the orderBy
$query .= " order by ";
// we start to build a coma separated list of items. First item won't have coma
// but every possible next will do
$seperator = "";
foreach($request->sortBy as $index => $sort)
{
// if column name is with -, then ordering is descending, otherwise ascending
if ( strpos($index, '-') === 0 )
{
$query .= $seperator . $sort . " ASC";
} else
{
$query .= $seperator . substr($sort, 1) . " DESC";
}
$separator = ',';
}
}
//DSResponse
$response = new DSResponse();
$products = R::getAll($query, $query_result['value']);
// get the count
$count = count($products);
$response->setData($products);
$response->setStartRow($request->startRow);
$response->setEndRow($request->endRow);
$response->setTotalRows($count);
$response->setStatus(0);
// sanity check, if no rows, return 0
if ($response->getEndRow() < 0 )
{
$response->setEndRow(0);
}
return $response;
}
|
Note a new method called buildAdvancedCriteria() is required which builds the query criteria from the AdvancedCriteria delivered by the FilterBuilder:
| Code Block | ||||
|---|---|---|---|---|
| ||||
private function buildAdvancedCriteria($request)
{
$advancedCriteria = $request->getAdvancedCriteria();
if(!empty($advancedCriteria))
{
$criteria_query = $this->buildCriterion($advancedCriteria);
$return = array();
$query$return['query'] = ' where '.$criteria_query['query'];
$return['value'] = $criteria_query['value'];
return $query$return;
}
return null;
} |
For parsing the tree itself, introduce a recursive function:
| Code Block | ||
|---|---|---|
| ||
private function buildCriterion($advancedCriteria)
{
$criterias = $advancedCriteria['criteria'];
$operator = $advancedCriteria['operator'];
$result = '';
$query = '';
$value = array();
foreach($criterias as $c)
{
if(isset($c['fieldName']))
$fn = $c['fieldName'];
if(isset($c['operator']))
$op = $c['operator'];
if(isset($c['value']))
{
if ($c['value'] === TRUE )
$val = '1';
else if($c['value'] === FALSE)
$val = '0';
else
$val = $c['value'];
}
if(isset($c['start']))
$start = $c['start'];
if(isset($c['end']))
$end = $c['end'];
if(isset($c['criteria']))
$criteria = $c['criteria'];
else
$criteria = null;
if(empty($criteria))
{
switch($op)
{
case 'equals':
$query = "$fn = '$val'?";
array_push($value, $val);
break;
case 'notEqual':
$query = "$fn != '$val'"; ?";
array_push($value, $val);
break;
case 'iEquals':
$query = "UPPER($fn) = ?";
array_push($value, "UPPER('{$val}')");
break;
case 'iNotEqual':
$query = "UPPER($fn) != ?";
array_push($value, "UPPER('{$val}')");
break;
case 'greaterThan':
$query = "$fn > '$val'?";
array_push($value, $val);
break;
case 'lessThan':
$query = "$fn < '$val'?";
array_push($value, $val);
break; case 'greaterOrEqual':
$query = "$fn >= '$val'";break;
case 'greaterOrEqual':
break;
case 'lessOrEqual': $query = "$fn >= ?";
array_push($value, $val); $query = "$fn <= '$val'";
break;
case 'containslessOrEqual':
$query = "$fn LIKE '%$val%'"; <= ?";
array_push($value, $val);
break;
case 'startsWithcontains':
$query = "$fn LIKE '$val%'"; ?";
array_push($value, "%{$val}%");
break;
case 'endsWithstartsWith':
$query = "$fn LIKE '%$val'?";
array_push($value, "{$val}%");
break; case 'iContains': $query = "UPPER($fn) LIKE UPPER('%$val%')";
break;
case 'iStartsWithendsWith':
$query = "UPPER($fn) LIKE UPPER('$val%')?";
array_push($value, "%{$val}");
break; case 'iEndsWith': $query = "UPPER($fn) LIKE UPPER('%$val')";
break;
case 'notContainsiContains':
$query = "$fn NOT LIKE '%$val%'?";
array_push($value, "%{$val}%");
break;
case 'notStartsWith': $query = "$fn NOT LIKE '$val%'";
break;
case 'notEndsWithiStartsWith':
$query = "UPPER($fn) NOT LIKE '%$val'?";
array_push($value, "UPPER('{$val}%')"); break;
case 'iNotContains': $query = "UPPER($fn) NOT LIKE UPPER('%$val%')";
break;
case 'iNotStartsWithiEndsWith':
$query = "UPPER($fn) NOT LIKE LIKE ?";
array_push($value, "UPPER('$val%%{$val}')"); break;
case 'iNotEndsWith': $query = "UPPER($fn) NOT LIKE UPPER('%$val')"; break; case 'isNull':
$query = "$fn IS NULL"; break;
break;
case 'notNullnotContains':
$query = "$fn ISNOT NOTLIKE NULL?";
array_push($value, "%{$val}%"); break; case 'equalsField': $query = "$fn LIKE CONCAT($val, '%')"; break; case 'iEqualsField': $query = "UPPER($fn) LIKE UPPER(CONCAT($val, '%'))";
break;
case 'notStartsWith': case 'iNotEqualField':
$query = "UPPER($fn) NOT LIKE UPPER(CONCAT($val, '%'))";
?";
array_push($value, "{$val}%");
break;
case 'notEqualFieldnotEndsWith':
$query = "$fn NOT LIKE CONCAT($val, '%')";
?";
array_push($value, "%{$val}");
break;
case 'greaterThanFieldiNotContains':
$query = "UPPER($fn > CONCAT($val, ') NOT LIKE ?";
array_push($value, "UPPER('%{$val}%')"; );
break;
case 'lessThanFieldiNotStartsWith':
$query = "UPPER($fn < CONCAT($val, ') NOT LIKE ?";
array_push($value, "UPPER('{$val}%')");
break;
case 'iNotEndsWith':
$query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER('%{$val}')");
break;
case 'greaterOrEqualFieldisNull':
$query = "$fn >= CONCAT($val, '%')IS NULL";
break;
case 'lessOrEqualFieldnotNull':
$query = "$fn <= CONCAT($val, '%')IS NOT NULL";
break;
case 'iBetweenInclusiveequalsField':
$query = "$fn BETWEEM $start AND $end"LIKE ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'betweenInclusiveiEqualsField':
$query = "UPPER($fn) BETWEEM $start AND $end"; LIKE ?";
break;
}
$result .= " ".$query." ".$operator." ";array_push($value, "UPPER(CONCAT('{$val}', '%'))"); }else
{
//break; build the list of subcriterias or criterions
$temp = $result;
case 'iNotEqualField':
$result1 = $this->buildCriterion($c $query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER(CONCAT('{$val}', '%'))");
break;
case 'notEqualField':
$query = "$fn NOT LIKE ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'greaterThanField':
$query = "$fn > ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'lessThanField':
$query = "$fn < ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'greaterOrEqualField':
$query = "$fn >= ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'lessOrEqualField':
$query = "$fn <= ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'iBetweenInclusive':
$query = "$fn BETWEEM ? AND ?";
array_push($value, $start);
array_push($value, $end);
break;
case 'betweenInclusive':
$query = "$fn BETWEEM ? AND ?";
array_push($value, $start);
array_push($value, $end);
break;
}
$result .= " ".$query." ".$operator." ";
}else
{
// build the list of subcriterias or criterions
$temp = $result;
$result1 = $this->buildCriterion($c);
$result = $temp . "(".$result1['query'].") ".$operator." ";
foreach($result1['value'] as $val)
{
array_push($value, $val);
$result = $temp . "(".$result1.") ".$operator." ";}
}
}
//remove the last operator ( and, or, not)
$result_query = substr($result, 0, strrpos($result, $operator));
return$advanced_critical['query'] = $result_query;
$advanced_critical['value'] = $value;
return $advanced_critical;
} |
If criterion exist (criteria property in this case will be null), simply build the query criteria and return it (this is the condition to exit from recursivity). If the criteria property is not null (the else branch), thencall the function again for each child criterion or criteria, and then assemble back everything using the specified operator before returning it to the caller.
...