protected function addOrDateQuery(Doctrine_Query $query, $field, $values)
{
$fieldName = $this->getFieldName($field);
if (isset($values['is_empty']) && $values['is_empty']) {
$query->orWhere(sprintf('%s.%s IS NULL', $query->getRootAlias(), $fieldName));
} else {
if (null !== $values['from'] && null !== $values['to']) {
$query->orWhere(sprintf('%s.%s >= ? AND %s.%s <= ?', $query->getRootAlias(), $fieldName, $query->getRootAlias(), $fieldName), array($values['from'], $values['to']));
} else {
if (null !== $values['from']) {
$query->orWhere(sprintf('%s.%s >= ?', $query->getRootAlias(), $fieldName), $values['from']);
} else {
if (null !== $values['to']) {
$query->orWhere(sprintf('%s.%s <= ?', $query->getRootAlias(), $fieldName), $values['to']);
}
}
}
}
}
protected function addOrDateQuery(Doctrine_Query $query, $fieldName, $values, $secondaryFromFieldName = null, $secondaryFromValues = null)
{
if (isset($values['is_empty']) && $values['is_empty']) {
$query->orWhere(sprintf('%s.%s IS NULL', $query->getRootAlias(), $fieldName));
} else {
if (null !== $values['from'] && null !== $values['to']) {
if (null !== $secondaryFromFieldName) {
$query->orWhere(sprintf('%s.%s > ? OR (%s.%s = ? AND %s.%s >= ?) AND %s.%s <= ?', $query->getRootAlias(), $secondaryFromFieldName, $query->getRootAlias(), $secondaryFromFieldName, $query->getRootAlias(), $fieldName, $query->getRootAlias(), $fieldName), array($secondaryFromValues['from'], $secondaryFromValues['from'], $values['from'], $values['to']));
} else {
$query->orWhere(sprintf('%s.%s >= ? AND %s.%s <= ?', $query->getRootAlias(), $fieldName, $query->getRootAlias(), $fieldName), array($values['from'], $values['to']));
}
} else {
if (null !== $values['from']) {
if (null !== $secondaryFromFieldName) {
$query->orWhere(sprintf('%s.%s > ? OR (%s.%s = ? AND %s.%s >= ?)', $query->getRootAlias(), $secondaryFromFieldName, $query->getRootAlias(), $secondaryFromFieldName, $query->getRootAlias(), $fieldName), array($secondaryFromValues['from'], $secondaryFromValues['from'], $values['from']));
} else {
$query->orWhere(sprintf('%s.%s >= ?', $query->getRootAlias(), $fieldName), $values['from']);
}
} else {
if (null !== $values['to']) {
$query->orWhere(sprintf('%s.%s <= ?', $query->getRootAlias(), $fieldName), $values['to']);
}
}
}
}
}
/**
* Adds a new condition to the current query
* $filter is the value to be filtered
* $op is the opreand to be used: =,>=, like, llike,REGEX,
* $completeField. use the index $completField['field'] to
* specify the field, to avoid ambiguous
*
* @param $filter
* @param $op
* @param $completeField
* @return Bvb_Grid_Source_Doctrine
*/
public function addCondition($filter, $op, $completeField)
{
$field = $completeField['field'];
/**
* FIX : #218
* We need to make sure to use HAVING when there is function
* in the select, as you cannot use these selected fields in the
* WHERE clause, and all others will use the WHERE clause
*/
$func = 'addWhere';
if (strpos($field, '(') !== false) {
$func = 'addHaving';
}
switch (strtolower($op)) {
case 'sqlexp':
$this->_query->{$func}($filter);
break;
case 'isnull':
$this->_query->{$func}($field . ' IS NULL ');
break;
case 'isnnotull':
$this->_query->{$func}($field . ' IS NOT NULL ');
break;
case 'empty':
$this->_query->{$func}($field . " =''");
break;
case 'equal':
case '=':
$this->_query->{$func}($field . ' = ?', $filter);
break;
case 'regex':
$this->_query->{$func}($field . " REGEXP ?", $filter);
break;
case 'rlike':
$this->_query->{$func}($field . " LIKE ?", $filter . "%");
break;
case 'llike':
$this->_query->{$func}($field . " LIKE ?", "%" . $filter);
break;
case '>=':
$this->_query->{$func}($field . " >= ?", $filter);
break;
case '>':
$this->_query->{$func}($field . " > ?", $filter);
break;
case '<>':
case '!=':
$this->_query->{$func}($field . " <> ?", $filter);
break;
case '<=':
$this->_query->{$func}($field . " <= ?", $filter);
break;
case '<':
$this->_query->{$func}($field . " < ?", $filter);
break;
case 'in':
$filter = explode(',', $filter);
$this->_query->whereIn($field, $filter);
break;
case '&':
case 'and':
case 'AND':
case 'flag':
case 'FLAG':
$this->_query->{$func}($field . " & ? <> 0", $filter);
break;
case 'range':
$start = substr($filter, 0, strpos($filter, '<>'));
$end = substr($filter, strpos($filter, '<>') + 2);
$this->_query->{$func}($field . " between ? and ?", array($start, $end));
break;
case '||':
$this->_query->orWhere($field . " LIKE ?", "%" . $filter . "%");
break;
case 'like':
default:
$this->_query->{$func}($field . " LIKE ?", "%" . $filter . "%");
break;
}
return $this;
}
public function routeTest10(Doctrine_Query $q)
{
$q->orWhere($q->getRootAlias() . '.is_on_homepage = ?', 0);
return $q->fetchOne();
}
/**
* Add custom search query (from the get param 'q')
*
* @param Doctrine_Query $q
* @param string $alias
* @param string $search
* @param boolean $useOr
*/
public static function add_search_str($q, $alias, $search, $useOr = null)
{
$a = $alias ? "{$alias}." : "";
$str = "({$a}bin_name like ?)";
$params = array("%{$search}%");
// try to also search User, if it's part of the query and has an alias
if ($alias) {
$parts = $q->getDqlPart('from');
foreach ($parts as $dql) {
if (preg_match("/{$alias}.User.*\$/", $dql, $matches)) {
$usr_alias = preg_replace("/{$alias}.User\\s*/", '', $matches[0]);
// must have alias
if ($usr_alias) {
$tmp = Doctrine_Query::create();
User::add_search_str($tmp, $usr_alias, $search);
$usrq = array_pop($tmp->getDqlPart('where'));
$usrp = $tmp->getFlattenedParams();
$str .= " or {$usrq}";
$params = array_merge($params, $usrp);
}
}
}
}
// add to query
if ($useOr) {
$q->orWhere($str, $params);
} else {
$q->addWhere($str, $params);
}
}
