/** * Attempt to pass search query on to the Source * * @param Doctrine_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str($q, $alias, $search, $useOr = null) { $from_parts = $q->getDqlPart('from'); foreach ($from_parts as $string_part) { if (preg_match("/{$alias}\\.Source *(\\w*)/", $string_part, $matches)) { $src_alias = isset($matches[1]) ? $matches[1] : null; Source::add_search_str($q, $src_alias, $search, $useOr); break; } } }
/** * Used to parse out the FROM and JOIN pieces of the DQL * and place it in the $_queryParts array for use * in many other places * * @return Bvb_Grid_Source_Doctrine */ protected function _setFromParts() { $return = array(); //Remove all 'as' instances $froms = $this->_removeAs($this->_query->getDqlPart('from')); foreach ($froms as $from) { $fields = explode(',', $from); $fields = array_map('trim', $fields); foreach ($fields as $field) { if (strpos(strtoupper($field), 'JOIN') === false) { $this->_queryParts = array_merge_recursive($this->_queryParts, $this->_explodeFrom($field)); } else { $join = explode('JOIN', $field); $join = array_map('trim', $join); $joinType = strtolower($join[0]); $this->_queryParts = array_merge_recursive($this->_queryParts, $this->_explodeJoin($join[1], $joinType)); } } } return $this; }
/** * Adds an adapter-specific LIMIT clause to the SELECT statement. * [ original code borrowed from Zend Framework ] * * License available at: http://framework.zend.com/license * * Copyright (c) 2005-2008, Zend Technologies USA, Inc. * All rights reserved. * * Redistribution and use in source and binary forms, with or without modification, * are permitted provided that the following conditions are met: * * * Redistributions of source code must retain the above copyright notice, * this list of conditions and the following disclaimer. * * * Redistributions in binary form must reproduce the above copyright notice, * this list of conditions and the following disclaimer in the documentation * and/or other materials provided with the distribution. * * * Neither the name of Zend Technologies USA, Inc. nor the names of its * contributors may be used to endorse or promote products derived from this * software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * * @param string $query * @param mixed $limit * @param mixed $offset * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html * @return string */ public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false, $isSubQuery = false, Doctrine_Query $queryOrigin = null) { if ($limit === false || !($limit > 0)) { return $query; } $orderby = stristr($query, 'ORDER BY'); if ($offset !== false && $orderby === false) { throw new Doctrine_Connection_Exception("OFFSET cannot be used in MSSQL without ORDER BY due to emulation reasons."); } $count = intval($limit); $offset = intval($offset); if ($offset < 0) { throw new Doctrine_Connection_Exception("LIMIT argument offset={$offset} is not valid"); } $orderbySql = $queryOrigin->getSqlQueryPart('orderby'); $orderbyDql = $queryOrigin->getDqlPart('orderby'); if ($orderby !== false) { $orders = $this->parseOrderBy(implode(', ', $queryOrigin->getDqlPart('orderby'))); for ($i = 0; $i < count($orders); $i++) { $sorts[$i] = stripos($orders[$i], ' desc') !== false ? 'DESC' : 'ASC'; $orders[$i] = trim(preg_replace('/\\s+(ASC|DESC)$/i', '', $orders[$i])); list($fieldAliases[$i], $fields[$i]) = strstr($orders[$i], '.') ? explode('.', $orders[$i]) : array('', $orders[$i]); $columnAlias[$i] = $queryOrigin->getSqlTableAlias($queryOrigin->getExpressionOwner($orders[$i])); $cmp = $queryOrigin->getQueryComponent($queryOrigin->getExpressionOwner($orders[$i])); $tables[$i] = $cmp['table']; $columns[$i] = $cmp['table']->getColumnName($fields[$i]); // TODO: This sould be refactored as method called Doctrine_Table::getColumnAlias(<column name>). $aliases[$i] = $columnAlias[$i] . '__' . $columns[$i]; } } // Ticket #1259: Fix for limit-subquery in MSSQL $selectRegExp = 'SELECT\\s+'; $selectReplace = 'SELECT '; if (preg_match('/^SELECT(\\s+)DISTINCT/i', $query)) { $selectRegExp .= 'DISTINCT\\s+'; $selectReplace .= 'DISTINCT '; } $fields_string = substr($query, strlen($selectReplace), strpos($query, ' FROM ') - strlen($selectReplace)); $field_array = explode(',', $fields_string); $field_array = array_shift($field_array); $aux2 = preg_split('/ as /i', $field_array); $aux2 = explode('.', end($aux2)); $key_field = trim(end($aux2)); $query = preg_replace('/^' . $selectRegExp . '/i', $selectReplace . 'TOP ' . ($count + $offset) . ' ', $query); if ($isSubQuery === true) { $query = 'SELECT TOP ' . $count . ' ' . $this->quoteIdentifier('inner_tbl') . '.' . $key_field . ' FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl'); } else { $query = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl'); } if ($orderby !== false) { $query .= ' ORDER BY '; for ($i = 0, $l = count($orders); $i < $l; $i++) { if ($i > 0) { // not first order clause $query .= ', '; } $query .= $this->modifyOrderByColumn($tables[$i], $columns[$i], $this->quoteIdentifier('inner_tbl') . '.' . $this->quoteIdentifier($aliases[$i])) . ' '; $query .= stripos($sorts[$i], 'asc') !== false ? 'DESC' : 'ASC'; } } if ($isSubQuery !== true) { $query .= ') AS ' . $this->quoteIdentifier('outer_tbl'); if ($orderby !== false) { $query .= ' ORDER BY '; for ($i = 0, $l = count($orders); $i < $l; $i++) { if ($i > 0) { // not first order clause $query .= ', '; } $query .= $this->modifyOrderByColumn($tables[$i], $columns[$i], $this->quoteIdentifier('outer_tbl') . '.' . $this->quoteIdentifier($aliases[$i])) . ' ' . $sorts[$i]; } } } return $query; }
public function addFriendActivityQuery(Doctrine_Query $q, $memberId, $isCheckApp = true) { if (is_null($memberId)) { $memberId = $this->getMyMemberId(); if (is_null($memberId)) { throw new LogicException('The user is not login.'); } } $dql = 'member_id = ?'; $dqlParams = array($memberId); $friendIds = Doctrine::getTable('MemberRelationship')->getFriendMemberIds($memberId); $flags = $this->getViewablePublicFlags(self::PUBLIC_FLAG_FRIEND); if ($friendIds) { $query = new Doctrine_Query(); $query->andWhereIn('member_id', $friendIds); $query->andWhereIn('public_flag', $flags); $dql .= ' OR ' . implode(' ', $query->getDqlPart('where')); $dqlParams = array_merge($dqlParams, $friendIds, $flags); } $q->andWhere('(' . $dql . ')', $dqlParams); $q->andWhere('in_reply_to_activity_id IS NULL'); if ($isCheckApp) { if ('mobile_frontend' == sfConfig::get('sf_app')) { $q->andWhere('is_mobile = ?', true); } else { $q->andWhere('is_pc = ?', true); } } }
/** * 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); } }
public function executeStatusesHomeTimeline(sfWebRequest $request) { $memberId = $this->getMemberId(); $validators = array('since_id' => new sfValidatorInteger(array('required' => false, 'min' => 1)), 'max_id' => new sfValidatorInteger(array('required' => false, 'min' => 1)), 'count' => new sfValidatorInteger(array('required' => false, 'max' => 200, 'min' => 1)), 'page' => new sfValidatorInteger(array('required' => false, 'min' => 1)), 'term_user' => new sfValidatorBoolean(array('required' => false))); $default = array('count' => 20, 'page' => 1, 'term_user' => false); $params = $this->validate($validators, $default); $q = Doctrine::getTable('ActivityData')->createQuery(); $dql = 'member_id = ?'; $dqlParams = array($memberId); $friendIds = Doctrine::getTable('MemberRelationship')->getFriendMemberIds($memberId); $flags = Doctrine::getTable('ActivityData')->getViewablePublicFlags(ActivityDataTable::PUBLIC_FLAG_FRIEND); if ($friendIds) { $query = new Doctrine_Query(); $query->andWhereIn('member_id', $friendIds); $query->andWhereIn('public_flag', $flags); $dql .= ' OR ' . implode(' ', $query->getDqlPart('where')); $dqlParams = array_merge($dqlParams, $friendIds, $flags); } $q->andWhere('(' . $dql . ')', $dqlParams); $q->andWhere('in_reply_to_activity_id IS NULL'); $q->limit($params['count']); if ($params['since_id']) { $q->andWhere('id > ?', $params['since_id']); } if ($params['max_id']) { $q->andWhere('id <= ?', $params['max_id']); } if (1 !== $params['page']) { $q->offset(($params['page'] - 1) * $params['count']); } $activities = $q->orderBy('id DESC')->execute(); $statuses = array(); foreach ($activities as $activity) { $statuses[] = array('status' => opActivityDataConverter::activityToStatus($activity, $params['term_user'])); } return $this->render(array('statuses' => $statuses)); }