/**
* help $this->queryBuilder to construct a PHQL object
* apply search rules based on the searchHelper conditions and return query
*
* @param \Phalcon\Mvc\Model\Query\BuilderInterface $query
* @param string $modelNameSpace
* @return \Phalcon\Mvc\Model\Query\BuilderInterface $query
*/
public function querySearcheHelper(\Phalcon\Mvc\Model\Query\BuilderInterface $query)
{
$searchFields = $this->searchHelper->getSearchFields();
if ($searchFields) {
// preprocess the search fields to see if any of the search names require preprocessing
// mostly just looking for || or type syntax otherwise process as default (and) WHERE clause
$processedSearchFields = array();
foreach ($searchFields as $fieldName => $fieldValue) {
$processedFieldName = $this->processSearchFields($fieldName);
$processedFieldValue = $this->processSearchFields($fieldValue);
$processedFieldQueryType = $this->processSearchFieldQueryType($processedFieldName, $processedFieldValue);
$processedSearchFields[] = array('queryType' => $processedFieldQueryType, 'fieldName' => $processedFieldName, 'fieldValue' => $processedFieldValue);
}
foreach ($processedSearchFields as $processedSearchField) {
switch ($processedSearchField['queryType']) {
case 'and':
$fieldName = $this->prependFieldNameNamespace($processedSearchField['fieldName']);
$operator = $this->determineWhereOperator($processedSearchField['fieldValue']);
$newFieldValue = $this->processFieldValue($processedSearchField['fieldValue'], $operator);
$query->andWhere("{$fieldName} {$operator} \"{$newFieldValue}\"");
break;
case 'or':
// make sure the field name is an array so we can use the same logic below for either circumstance
if (!is_array($processedSearchField['fieldName'])) {
$fieldNameArray = array($processedSearchField['fieldName']);
} else {
$fieldNameArray = $processedSearchField['fieldName'];
}
// make sure the field value is an array so we can use the same logic below for either circumstance
if (!is_array($processedSearchField['fieldValue'])) {
$fieldValueArray = array($processedSearchField['fieldValue']);
} else {
$fieldValueArray = $processedSearchField['fieldValue'];
}
foreach ($fieldNameArray as $fieldName) {
$fieldName = $this->prependFieldNameNamespace($fieldName);
foreach ($fieldValueArray as $fieldValue) {
$operator = $this->determineWhereOperator($fieldValue);
$newFieldValue = $this->processFieldValue($fieldValue, $operator);
$query->orWhere("{$fieldName} {$operator} \"{$newFieldValue}\"");
}
}
break;
}
}
}
return $query;
}
/**
* help $this->queryBuilder to construct a PHQL object
* apply search rules based on the searchHelper conditions and return query
*
* @param \Phalcon\Mvc\Model\Query\BuilderInterface $query
* @param string $modelNameSpace
* @return \Phalcon\Mvc\Model\Query\BuilderInterface $query
*/
public function querySearcheHelper(\Phalcon\Mvc\Model\Query\BuilderInterface $query)
{
$searchFields = $this->searchHelper->getSearchFields();
if ($searchFields) {
// pre-process the search fields to see if any of the search names require pre-processing
// mostly just looking for || or type syntax otherwise process as default (and) WHERE clause
$processedSearchFields = array();
foreach ($searchFields as $fieldName => $fieldValue) {
$processed = $this->processFilterField($fieldName, $fieldValue, $query);
if ($processed !== false) {
$processedSearchFields[] = $processed;
}
}
foreach ($processedSearchFields as $processedSearchField) {
switch ($processedSearchField['queryType']) {
case 'and':
$fieldName = $this->prependFieldNameNamespace($processedSearchField['fieldName']);
$operator = $this->determineWhereOperator($processedSearchField['fieldValue']);
$newFieldValue = $this->processFieldValue($processedSearchField['fieldValue'], $operator);
// $query->andWhere("$fieldName $operator \"$newFieldValue\"");
if ($operator === 'IS NULL') {
$query->andWhere("{$fieldName} {$operator}");
} else {
$randomName = 'rand' . rand(1, 1000000);
$query->andWhere("{$fieldName} {$operator} :{$randomName}:", array($randomName => $newFieldValue));
}
break;
case 'or':
// format field name(s) is an array so we can use the same logic below for either circumstance
if (!is_array($processedSearchField['fieldName'])) {
$fieldNameArray = array($processedSearchField['fieldName']);
} else {
$fieldNameArray = $processedSearchField['fieldName'];
}
// format field value(s) is an array so we can use the same logic below for either circumstance
if (!is_array($processedSearchField['fieldValue'])) {
$fieldValueArray = array($processedSearchField['fieldValue']);
} else {
$fieldValueArray = $processedSearchField['fieldValue'];
}
// update to bind params instead of using string concatination
$queryArr = [];
$valueArr = [];
$count = 1;
foreach ($fieldNameArray as $fieldName) {
$fieldName = $this->prependFieldNameNamespace($fieldName);
foreach ($fieldValueArray as $fieldValue) {
$marker = 'marker' . $count;
$operator = $this->determineWhereOperator($fieldValue);
$newFieldValue = $this->processFieldValue($fieldValue, $operator);
if ($operator === 'IS NULL') {
$queryArr[] = "{$fieldName} {$operator}";
} else {
$queryArr[] = "{$fieldName} {$operator} :{$marker}:";
$valueArr[$marker] = $newFieldValue;
}
$count++;
}
}
$sql = implode(' OR ', $queryArr);
$query->andWhere($sql, $valueArr);
break;
}
}
}
return $query;
}
