PHP Doctrine_Query::addSelect方法代码示例

 public function addJoinQuery(Doctrine_Query $q)
 {
     $rootAlias = $q->getRootAlias();
     $q->leftJoin($rootAlias . '.Category c')->leftJoin($rootAlias . '.Tags t')->leftJoin($rootAlias . '.CreatedBy u');
     //->leftJoin($rootAlias . '.Comments m');
     $q->addSelect($rootAlias . '.*, c.*, t.*, u.*');
     $q->addSelect('(SELECT COUNT(*) FROM zsBlogComment m WHERE m.article_id = ' . $rootAlias . '.id) as num_comments');
     //$q->addSelect('COUNT(m.id) num_comments');
     //$q->groupBy($rootAlias.'.id');
     return $q;
 }

 /**
  * Used to set SELECT values to the DQL when
  * no SELECT is provided.  We will just add
  * ALL columns for all tables given.
  *
  * NOTE: Since no SELECT was provided, to access these
  * from within the Bvb_Grid_Data class, you will need to
  * use the table alias + "_" + column name
  *
  * <code>
  * $grid->setGridColumns(array('co_code', 'co_name', 'co_continent', 'ci_name'));
  * </code>
  *
  * @return void
  */
 protected function _findAndSetSelect()
 {
     $return = array();
     //Make sure we have the FROM set
     $this->_setFromParts();
     $fromTableModel = $this->_queryParts['from']['tableModel'];
     $fromClass = Doctrine::getTable($fromTableModel);
     $fromColumns = array_keys($fromClass->getColumns());
     $fromAlias = $this->_queryParts['from']['alias'];
     foreach ($fromColumns as $fromColumn) {
         /**
          * Do this check here because a DQL with no JOIN,
          * does not need a table alias
          *
          * @var string
          */
         $addColumn = !empty($fromAlias) ? $fromAlias . '.' . $fromColumn : $fromColumn;
         $this->_query->addSelect($addColumn);
     }
     $joins = $this->_queryParts['join'];
     if (!empty($joins)) {
         foreach ($joins as $joinType) {
             foreach ($joinType as $join) {
                 $joinClass = Doctrine::getTable($join['tableModel']);
                 $joinColumns = array_keys($joinClass->getColumns());
                 foreach ($joinColumns as $joinColumn) {
                     $this->_query->addSelect($join['alias'] . '.' . $joinColumn);
                 }
             }
         }
     }
 }

 /**
  * getSetting
  * pulls the sfSetting object for a given setting
  * 
  * @param string $setting 
  * @static
  * @access public
  * @return object sfSetting
  */
 static function getSetting($setting)
 {
     if (!is_string($setting) || empty($setting)) {
         return 0;
     }
     // If all the settings have been requested, there's no need to check for
     // individuals. This avoids additional queries later on.
     if (sfContext::getInstance()->getRequest()->hasAttribute('AllsfSettings')) {
         $settings = sfContext::getInstance()->getRequest()->getAttribute('AllsfSettings');
     } else {
         $settings = sfContext::getInstance()->getRequest()->getAttribute('sfSettings');
     }
     if (isset($settings[$setting])) {
         $obj = $settings[$setting];
         return $obj;
     } else {
         // Setting was not pre-loaded via ->load() but we'll be nice and retrieve
         // the setting anyhow:
         $query = new Doctrine_Query();
         $query->addSelect("s.*");
         $query->addFrom("sfSetting s");
         $query->addWhere("s.name = :name", array(":name" => $setting));
         if ($obj = $query->limit(1)->execute()->getFirst()) {
             // Store this setting in memory for later retrieval to avoid a second
             // query for the same setting:
             $settings[$obj->getName()] = $obj;
             sfContext::getInstance()->getRequest()->setAttribute('sfSettings', $settings);
             // return it:
             return $obj;
         } else {
             return 0;
         }
     }
 }

 public function testTicket()
 {
     $q = new Doctrine_Query();
     // simple query with deep relations
     $q->addSelect('Resume.id, Level.id, Level.label')->from('ticket384_Resume Resume')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
     try {
         // get the wrong resultset
         $aResult = $q->fetchArray();
         $this->fail();
     } catch (Doctrine_Query_Exception $e) {
         $this->pass();
     }
     $q->free();
     // now correct
     // we have to select at least KnownLanguages.id in order to get the Levels,
     // which are only reachable through the KnownLanguages, hydrated properly.
     $q = new Doctrine_Query();
     $q->addSelect('Resume.id, Level.id, Level.label, KnownLanguages.id')->from('ticket384_Resume Resume')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
     $aResult = $q->fetchArray();
     // should be setted
     $bSuccess = isset($aResult[0]['KnownLanguages'][0]['Level']);
     $this->assertTrue($bSuccess);
     if (!$bSuccess) {
         $this->fail('fetchArray doesnt hydrate nested child relations, if parent doesnt have a column selected');
     }
 }

 /**
  * Test the existence expected indexes
  */
 public function testTicket()
 {
     $q = new Doctrine_Query();
     // simple query with deep relations
     $q->addSelect('Resume.id, Person.id, Person.name, KnownLanguages.id, Level.label, Language.label')->from('ticket912_Resume Resume')->leftJoin('Resume.Person Person')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
     $aResult = $q->fetchArray();
     // should be setted..
     $issetLevel = isset($aResult[0]['KnownLanguages'][0]['Level']);
     $issetLanguage = isset($aResult[0]['KnownLanguages'][0]['Language']);
     $this->assertTrue($issetLevel);
     $this->assertTrue($issetLanguage);
 }

 public function testTicket()
 {
     $q1 = new Doctrine_Query();
     $q1->select('p.*');
     $q1->from('Testing_Product p');
     $q1->where('p.id = ?', $this->p1['id']);
     $q1->addSelect('a.*, ad.*');
     $q1->leftJoin('p.Attributes a');
     $q1->leftJoin('a.Definition ad');
     $q2 = new Doctrine_Query();
     $q2->select('p.*');
     $q2->from('Testing_Product p');
     $q2->where('p.id = ?', $this->p2['id']);
     $q2->addSelect('a.*, ad.*');
     $q2->leftJoin('p.Attributes a');
     $q2->leftJoin('a.Definition ad');
     // This query works perfect
     $r1 = $q1->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
     //var_dump($r1);
     // This query throws an exception!!!
     $r2 = $q2->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
     //$r2 = $q2->execute();
     //var_dump($r2);
 }

 public function testLiteralEnumValueConversionSupportsJoins()
 {
     $q = new Doctrine_Query($this->connection);
     $q->addSelect('e.*')->addSelect('e3.*')->from('EnumTest e')->leftJoin('e.Enum3 e3')->where("e.status = 'verified'")->execute();
     $this->assertEqual($q->getSqlQuery(), "SELECT e.id AS e__id, e.status AS e__status, e.text AS e__text, e2.text AS e2__text FROM enum_test e LEFT JOIN enum_test3 e2 ON e.text = e2.text WHERE (e.status = 'verified')");
 }

 /**
  * Create a "status" column based on job state
  *
  * @param Doctrine_Query $q
  */
 protected function add_status($q)
 {
     $sel = 'select case';
     foreach ($this->status_queries as $code => $query) {
         $sel .= " when {$query} then '{$code}'";
     }
     $sel .= " else 'U' end";
     $q->addSelect("({$sel}) as jq_status");
 }

 /**
  * The first time you call addSelect (assuming that you didn't do an
  * explicit $q->select() query), all the joined relations will be
  * auto-selected.  After this point, however, your select statement will
  * be explicit, so if you continue to join relations, they will not be
  * automatically included in the select.
  *
  * @param string  $select
  * @return Doctrine_Query
  */
 public function addSelect($select)
 {
     if (!$this->is_explicit_select) {
         // get the "from" parts, and add them to the select
         $this->getSqlQuery();
         // trigger the dql parser
         $aliases = array_keys($this->_queryComponents);
         foreach ($aliases as $idx => $a) {
             $aliases[$idx] .= '.*';
         }
         $this->select(implode(', ', $aliases));
     }
     // now add the select statement
     return parent::addSelect($select);
 }

 /**
  * @access private
  *
  * @author Jannis Moßhammer <jannis.mosshammer@netways.de>
  **/
 protected function modifyImpl(Doctrine_Query &$o)
 {
     $o->setAliasDefs($this->mainAlias, $this->aliasDefs);
     $o->setDefaultJoinType($this->defaultJoinType);
     foreach ($this->fields as $field) {
         $o->addSelect($field);
     }
     $o->distinct($this->isDistinct());
     $o->from($this->target . " " . $this->mainAlias);
     foreach ($this->staticWhereConditions as $cond) {
         if (isset($cond[1]) && $cond[1] != null) {
             $o->addWhere($cond[0], $cond[1]);
         } else {
             $o->addWhere($cond[0]);
         }
     }
 }

 protected function modifyImpl(Doctrine_Query &$o)
 {
     if (!$this->ignoreIds) {
         $table = $o->getConnection()->getTable($this->getTarget());
         $keys = $table->getIdentifierColumnNames();
         $o->addSelect(implode($keys));
     } else {
         $o->disableAutoIdentifierFields(true);
     }
     foreach ($this->additionalSelects as $alias => $select) {
         $o->addSelect($select . (is_numeric($alias) ? "" : " AS " . $alias));
     }
     $db = $this->getContext()->getDatabaseManager()->getDatabase('icinga');
     // check if retained state must be respected
     if (method_exists($db, "useRetained")) {
         /*
          * the core with idomod dumps 2 different config types
          * idomod.cfg:config_output_options
          * 1 = original config => config_type = 0
          * 2 = retained config => config_type = 1
          * 3 = both, both config_types are available
          */
         if ($this->retainedAlias) {
             $o->andWhere($this->retainedAlias . ".config_type= ?", $db->useRetained() ? "1" : "0");
         }
     }
     if ($this->getTarget() == "IcingaObjects") {
         $o->andWhere($this->mainAlias . ".is_active = 1");
     }
     foreach ($this->forceGroup as $group) {
         $o->addGroupBy($group);
     }
     parent::modifyImpl($o);
 }

 /**
  *
  * @param Doctrine_Query $query
  * @param FilterPetitionForm $filter
  * @return Doctrine_Query
  */
 public function filter(Doctrine_Query $query, $filter)
 {
     if (!$filter) {
         return $query;
     }
     /* @var $filter policatFilter */
     if ($filter->getValue(self::FILTER_CAMPAIGN)) {
         $query->andWhere('p.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
     }
     if ($filter->getValue(self::FILTER_KIND)) {
         $query->andWhere('p.kind = ?', $filter->getValue(self::FILTER_KIND));
     }
     if ($filter->getValue(self::FILTER_STATUS)) {
         $query->andWhere('p.status = ?', $filter->getValue(self::FILTER_STATUS));
     } else {
         $query->andWhere('p.status != ?', Petition::STATUS_DELETED);
     }
     if ($filter->getValue(self::FILTER_START)) {
         $query->andWhere('p.start_at > ?', $filter->getValue(self::FILTER_START));
     }
     if ($filter->getValue(self::FILTER_END)) {
         $query->andWhere('p.end_at < ?', $filter->getValue(self::FILTER_END));
     }
     if ($filter->getValue(self::FILTER_ORDER)) {
         switch ($filter->getValue(self::FILTER_ORDER)) {
             case self::ORDER_CAMPAIGN_ASC:
                 $query->leftJoin('p.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('p.campaign_id ASC')->addOrderBy('p.id ASC');
                 break;
             case self::ORDER_CAMPAIGN_DESC:
                 $query->leftJoin('p.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('p.campaign_id DESC')->addOrderBy('p.id DESC');
                 break;
             case self::ORDER_ACTION_ASC:
                 $query->orderBy('p.name ASC');
                 break;
             case self::ORDER_ACTION_DESC:
                 $query->orderBy('p.name DESC');
                 break;
             case self::ORDER_STATUS_ASC:
                 $query->orderBy('p.status ASC')->addOrderBy('p.id ASC');
                 break;
             case self::ORDER_STATUS_DESC:
                 $query->orderBy('p.status DESC')->addOrderBy('p.id DESC');
                 break;
             case self::ORDER_ID_ASC:
                 $query->orderBy('p.id ASC');
                 break;
             case self::ORDER_ID_DESC:
                 $query->orderBy('p.id DESC');
                 break;
             case self::ORDER_ACTIVITY_ASC:
                 $query->orderBy('p.activity_at ASC');
                 break;
             case self::ORDER_ACTIVITY_DESC:
                 $query->orderBy('p.activity_at DESC');
                 break;
             case self::ORDER_TRENDING:
                 $query->select('p.*');
                 $query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at  and z.petition_id = p.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
                 $query->orderBy('signings24 DESC, p.activity_at DESC, p.id DESC');
                 break;
         }
     }
     if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
         $query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.petition_id = p.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
     }
     return $query;
 }

 /**
  * sortable STATUS column (to sort by status MEANING, not char val)
  *
  * @param Doctrine_Query $q
  */
 protected function add_status_sort($q)
 {
     $stat_sort = array(TankSource::$STATUS_ERROR, TankSource::$STATUS_CONFLICT, TankSource::$STATUS_LOCKED, TankSource::$STATUS_RESOLVED, TankSource::$STATUS_DONE, TankSource::$STATUS_NEW);
     $sel = 'select case';
     foreach ($stat_sort as $idx => $stat) {
         $sel .= " when t.tsrc_status = '{$stat}' then {$idx}";
     }
     $sel .= ' else 99 end';
     $q->addSelect("({$sel}) as status_sort");
 }

 /**
  *
  * @param Doctrine_Query $query
  * @param FilterWidgetForm $filter
  * @return Doctrine_Query
  */
 public function filter(Doctrine_Query $query, $filter)
 {
     if (!$filter) {
         return $query;
     }
     //    print_r($filter->getValues()); die('y');
     /* @var $filter  policatFilter */
     if ($filter->getValue(self::FILTER_CAMPAIGN)) {
         $query->andWhere('w.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
     }
     if ($filter->getValue(self::FILTER_STATUS)) {
         $query->andWhere('w.status = ?', $filter->getValue(self::FILTER_STATUS));
     }
     if ($filter->getValue(self::FILTER_LANGUAGE)) {
         $query->leftJoin('w.PetitionText pt_for_lang')->andWhere('pt_for_lang.language_id = ?', $filter->getValue(self::FILTER_LANGUAGE));
     }
     $petition_joined = false;
     if ($filter->getValue(self::FILTER_START)) {
         if (!$petition_joined) {
             $query->leftJoin('w.Petition p_filter');
             $petition_joined = true;
         }
         $query->andWhere('p_filter.start_at > ?', $filter->getValue(self::FILTER_START));
     }
     if ($filter->getValue(self::FILTER_END)) {
         if (!$petition_joined) {
             $query->leftJoin('w.Petition p_filter');
             $petition_joined = true;
         }
         $query->andWhere('p_filter.end_at < ?', $filter->getValue(self::FILTER_END));
     }
     if ($filter->getValue(self::FILTER_PETITION)) {
         $query->andWhere('w.petition_id = ?', $filter->getValue(self::FILTER_PETITION));
     }
     if ($filter->getValue(self::FILTER_ORDER)) {
         switch ($filter->getValue(self::FILTER_ORDER)) {
             case self::ORDER_CAMPAIGN_ASC:
                 $query->leftJoin('w.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('w.campaign_id ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_CAMPAIGN_DESC:
                 $query->leftJoin('w.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('w.campaign_id DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_ACTION_ASC:
                 if (!$petition_joined) {
                     $query->leftJoin('w.Petition p_filter');
                     $petition_joined = true;
                 }
                 $query->orderBy('p_filter.name ASC')->addOrderBy('w.petition_id ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_ACTION_DESC:
                 if (!$petition_joined) {
                     $query->leftJoin('w.Petition p_filter');
                     $petition_joined = true;
                 }
                 $query->orderBy('p_filter.name DESC')->addOrderBy('w.petition_id DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_STATUS_ASC:
                 $query->orderBy('w.status ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_STATUS_DESC:
                 $query->orderBy('w.status DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_WIDGET_ASC:
                 $query->orderBy('w.id ASC');
                 break;
             case self::ORDER_WIDGET_DESC:
                 $query->orderBy('w.id DESC');
                 break;
             case self::ORDER_LANGUAGE_ASC:
                 $query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_LANGUAGE_DESC:
                 $query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_ACTIVITY_ASC:
                 $query->orderBy('w.activity_at ASC');
                 break;
             case self::ORDER_ACTIVITY_DESC:
                 $query->orderBy('w.activity_at DESC');
                 break;
             case self::ORDER_TRENDING:
                 $query->select('w.*');
                 $query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at  and z.widget_id = w.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
                 $query->orderBy('signings24 DESC, w.activity_at DESC, w.id DESC');
                 break;
         }
     }
     if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
         $query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.widget_id = w.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
     }
     return $query;
 }

 /**
  * Retrieves tags with the number of taggings for a given set of models, can be accessed using ->$model
  *
  * @param Array $models
  * @param Doctrine_Query $q
  * @return Doctrine_Query
  */
 public function queryTagsWithCountsByModel($models, $q = null)
 {
     $q->leftJoin('r.Tagging tg')->addSelect('r.*');
     foreach ($models as $model) {
         $q->addSelect("SUM(IF(tg.taggable_model = '{$model}' , 1, 0)) AS " . $model . "Count");
     }
     $q->groupBy('r.id');
     return $q;
 }