PHP sfDoctrinePager::getQuery方法代码示例

 /**
  * Fetch images from pool according to any default tag(s) option specified 
  * in the global schema.yml.
  * Override added to specify per tag in MooEditable
  * 
  * @return sfDoctrinePager
  */
 public function getPager($per_page = 12, $page = 1, $tagged_object = null, $tag = null)
 {
     $pager = new sfDoctrinePager($this->getClassnameToReturn(), $per_page);
     $im = new sfImagePoolImage();
     if (!$im->option('tagging')) {
         $tagged_object = $tag = null;
     }
     $im->free(true);
     if (isset($tagged_object) && ($tag = $tagged_object->getTagRestriction())) {
         $query = TagTable::getObjectTaggedWithQuery($this->getClassnameToReturn(), $tag, $pager->getQuery(), array('nb_common_tags' => 1));
         $pager->setQuery($query);
     } else {
         if (!empty($tag)) {
             // If only tags we have no taggable object
             // So get images tagged with this query and do a whereIn on the ids
             $tags = explode(',', $tag);
             $images = TagTable::getObjectTaggedWith($tags, array('model' => 'sfImagePoolImage', 'nb_common_tags' => 1));
             $image_ids = array();
             foreach ($images as $image) {
                 $image_ids[] = $image->id;
             }
             if (!empty($image_ids)) {
                 $pager->getQuery()->whereIn('sfImagePoolImage.id', $image_ids);
             } else {
                 $pager->getQuery()->where('false');
             }
             // we have no images tagged
         }
     }
     $pager->getQuery()->orderBy('updated_at DESC');
     $pager->setPage($page);
     $pager->init();
     return $pager;
 }

 /**
  * Retrieves a pager of recent associations 
  * @param string model name
  * @return Pager
  */
 public static function listRecentChangesPager($doc_id = null, $users = null, $orderby = null, $npp = 25)
 {
     // TODO: possibility to filter on association type?
     $pager = new sfDoctrinePager('AssociationLog', $npp);
     $q = $pager->getQuery();
     $q->select('al.*, mi.name, mi.search_name, li.name, li.search_name, u.username, u.login_name, u.topo_name')->from('AssociationLog al')->leftJoin('al.mainI18n mi')->leftJoin('al.linkedI18n li')->leftJoin('al.user_private_data u');
     // filter on a specific doc if needed
     if ($doc_id) {
         $q->addWhere('al.main_id=? OR al.linked_id=?', array($doc_id, $doc_id));
     }
     // filter on a specific user if needed
     if ($users) {
         $users = explode('-', $users);
         if (count($users)) {
             $where_ids = array();
             foreach ($users as $uid) {
                 $where_ids[] = '?';
             }
             $where_ids = implode(', ', $where_ids);
             if (count($where_ids) == 1) {
                 $where = ' = ' . $where_ids;
             } else {
                 $where = ' IN ( ' . $where_ids . ' )';
             }
             $q->addWhere('u.id' . $where, $users);
         }
     }
     if (empty($orderby)) {
         $q->orderBy('al.associations_log_id DESC');
         // ~ decreasing time (but faster, since there is an index on this field).
     } elseif ($orderby == 'uid') {
         $q->orderBy('u.id ASC');
     }
     return $pager;
 }

$t->is($pager->getQuery()->getSqlQuery(), 'SELECT a.id AS a__id, a.name AS a__name, a.type AS a__type FROM author a WHERE (a.id > 0) LIMIT 25');
$t->ok($pager->isFirstPage());
$pager->setPage(2);
$pager->init();
$t->is($pager->getQuery()->getSqlQuery(), 'SELECT a.id AS a__id, a.name AS a__name, a.type AS a__type FROM author a WHERE (a.id > 0) LIMIT 25 OFFSET 25');
$t->is($pager->getQuery()->count(), $total);
$t->ok($pager->isLastPage());
$results = $pager->getResults();
$t->is(gettype($results), 'object');
$t->is(get_class($results), 'Doctrine_Collection');
$t->is(count($results), $numPerPage);
$t->is($pager->getCountQuery()->count(), $total);
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setTableMethod('testTableMethod');
$pager->setPage(1);
$pager->init();
$results = $pager->getResults(Doctrine::HYDRATE_ARRAY);
$t->is(gettype($results), 'array');
$t->is(count($results), $numPerPage);
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setTableMethod('testTableMethod2');
$pager->setQuery(Doctrine_Query::create()->from('Author a')->where('a.id < 9999999'));
$pager->setPage(1);
$pager->init();
$t->is($pager->getQuery()->getSqlQuery(), 'SELECT a.id AS a__id, a.name AS a__name, a.type AS a__type FROM author a WHERE (a.id < 9999999 AND a.id > 0) LIMIT 25');
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setQuery(Doctrine_Query::create()->from('Author a')->where('a.id < 9999999'));
$pager->setPage(1);
$pager->init();
$t->is($pager->getQuery()->getSqlQuery(), 'SELECT a.id AS a__id, a.name AS a__name, a.type AS a__type FROM author a WHERE (a.id < 9999999) LIMIT 25');

 /**
  * Get the query for the pager.
  *
  * @return Doctrine_Query
  */
 public function getQuery($withI18n = false)
 {
     if ($withI18n && dmDb::table($this->class)->hasI18n()) {
         return parent::getQuery()->withI18n();
     }
     return parent::getQuery();
 }

 public static function retrieveConditions($days)
 {
     $pager = new sfDoctrinePager('Outing', 10);
     $q = $pager->getQuery();
     $q->select('m.date, m.activities, m.conditions_status, m.up_snow_elevation, m.down_snow_elevation, ' . 'm.access_elevation, mi.name, mi.search_name, mi.conditions, mi.conditions_levels, mi.weather, mi.culture' . 'g0.type, g0.linked_id, ai.name, m.max_elevation')->from('Outing m')->leftJoin('m.OutingI18n mi')->where("m.redirects_to IS NULL AND age(date) < interval '{$days} days'")->orderBy('m.date DESC, m.id DESC');
     self::joinOnRegions($q);
     // applying user filters
     if (c2cPersonalization::getInstance()->isMainFilterSwitchOn()) {
         self::filterOnLanguages($q);
         self::filterOnActivities($q);
         self::filterOnRegions($q);
     }
     return $pager;
 }

$pager->setPage(1);
$pager->init();
$t->is($pager->getQuery()->getSql(), 'SELECT a.id AS a__id, a.name AS a__name FROM author a WHERE a.id > 0 LIMIT 25');
$pager->setPage(2);
$pager->init();
$t->is($pager->getQuery()->getSql(), 'SELECT a.id AS a__id, a.name AS a__name FROM author a WHERE a.id > 0 LIMIT 25 OFFSET 25');
$results = $pager->getResults();
$t->is(gettype($results), 'object');
$t->is(get_class($results), 'Doctrine_Collection');
$t->is(count($results), $numPerPage);
$t->is($pager->getQuery()->count(), $total);
$t->is($pager->getCountQuery()->count(), $total);
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setTableMethod('testTableMethod');
$pager->setPage(1);
$pager->init();
$results = $pager->getResults('array');
$t->is(gettype($results), 'array');
$t->is(count($results), $numPerPage);
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setTableMethod('testTableMethod2');
$pager->setQuery(Doctrine_Query::create()->from('Author a')->where('a.id < 9999999'));
$pager->setPage(1);
$pager->init();
$t->is($pager->getQuery()->getSql(), 'SELECT a.id AS a__id, a.name AS a__name FROM author a WHERE a.id < 9999999 AND a.id > 0 LIMIT 25');
$pager = new sfDoctrinePager('Author', $numPerPage);
$pager->setQuery(Doctrine_Query::create()->from('Author a')->where('a.id < 9999999'));
$pager->setPage(1);
$pager->init();
$t->is($pager->getQuery()->getSql(), 'SELECT a.id AS a__id, a.name AS a__name FROM author a WHERE a.id < 9999999 LIMIT 25');