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); } }