PHP Horde_Db_Adapter::addLimitOffset方法代码示例

 /**
  *  Return the current value of the modseq. We take the MAX of the
  *  horde_histories table instead of the value of the horde_histories_modseq
  *  table to ensure we never miss an entry if we query the history system
  *  between the time we call nextModSeq() and the time the new entry is
  *  written.
  *
  * @param string $parent  Restrict to entries a specific parent.
  *
  * @return integer|boolean  The highest used modseq value, false if no history.
  */
 public function getHighestModSeq($parent = null)
 {
     $sql = 'SELECT history_modseq FROM horde_histories';
     if (!empty($parent)) {
         $sql .= ' WHERE object_uid LIKE ' . $this->_db->quote($parent . ':%');
     }
     $sql .= ' ORDER BY history_modseq DESC';
     $sql = $this->_db->addLimitOffset($sql, array('limit' => 1));
     try {
         $modseq = $this->_db->selectValue($sql);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_History_Exception($e);
     }
     if (is_null($modseq) || $modseq === false) {
         try {
             $modseq = $this->_db->selectValue('SELECT MAX(history_modseq) FROM horde_histories_modseq');
         } catch (Horde_Db_Exception $e) {
             throw new Horde_History_Exception($e);
         }
         if (!empty($modseq)) {
             return $modseq;
         } else {
             return false;
         }
     }
     return $modseq;
 }

 /**
  */
 public function favouriteRecipients($limit, $filter = null)
 {
     /* Build the SQL query. */
     $where = '';
     if (!empty($filter)) {
         $filter = array_map(array($this->_db, 'quote'), $filter);
         $where = sprintf(' AND sentmail_action in (%s)', implode(', ', $filter));
     }
     $query = sprintf('SELECT sentmail_recipient, count(*) AS sentmail_count FROM %s WHERE sentmail_who = %s AND sentmail_success = 1%s GROUP BY sentmail_recipient ORDER BY sentmail_count DESC', $this->_params['table'], $this->_db->quote($GLOBALS['registry']->getAuth()), $where);
     /* Execute the query. */
     try {
         $query = $this->_db->addLimitOffset($query, array('limit' => $limit));
         return $this->_db->selectValues($query);
     } catch (Horde_Db_Exception $e) {
         return array();
     }
 }

 /**
  * Return users related to a given user along with a similarity rank.
  */
 public function getSimilarUsers($args)
 {
     $args['userId'] = current($this->_userManager->ensureUsers($args['userId']));
     $radius = isset($args['radius']) ? (int) $args['radius'] : $this->_defaultRadius;
     $sql = 'SELECT others.user_id, (others.count - self.count) AS rank FROM ' . $this->_t('user_tag_stats') . ' others INNER JOIN (SELECT tag_id, count FROM ' . $this->_t('user_tag_stats') . ' WHERE user_id = ' . (int) $args['userId'] . ' AND count >= ' . $radius . ') self ON others.tag_id = self.tag_id ORDER BY rank DESC';
     if (isset($args['limit'])) {
         $sql = $this->_db->addLimitOffset($sql, array('limit' => $args['limit']));
     }
     return $this->_db->selectAssoc($sql);
 }

 /**
  * Lists a slice of the image ids in the given gallery.
  *
  * @param array $params  Filter parameters.
  *<pre>
  *  integer|array 'gallery_id'  - A gallery id to list images from
  *  integer 'offset'            - The image to start listing from
  *  integer 'limit'             - How many images to return
  *  array|string 'fields'       - The fields to return
  *  string 'sort'               - The field to sort by.
  *  array  'filter'             - Additional filters. Each element is an
  *                                array containing 'property', 'op', and
  *                                'value' keys. Passing 'IN' as the 'op'
  *                                and an array as 'value' will produce a
  *                                SQL IN conditional.
  *</pre>
  *
  * @return array  An array of images. Either an array of ids, or an array
  *                of field values, keyed by id.
  * @throws Ansel_Exception, InvalidArgumentException
  */
 public function listImages(array $params = array())
 {
     $params = new Horde_Support_Array($params);
     if (is_array($params['fields'])) {
         $field_count = count($params['fields']);
         $params['fields'] = implode(', ', $params['fields']);
     } elseif ($params['fields'] == '*') {
         // The count is not important, as long as it's > 1
         $field_count = 2;
     } else {
         $field_count = substr_count($params->get('fields', 'image_id'), ',') + 1;
     }
     if (is_array($params['sort'])) {
         $params['sort'] = implode(', ', $params['sort']);
     }
     if (is_array($params['gallery_id'])) {
         $query_where = 'WHERE gallery_id IN (' . implode(',', $params['gallery_id']) . ')';
     } elseif ($params['gallery_id']) {
         $query_where = 'WHERE gallery_id = ' . $params['gallery_id'];
     } else {
         $query_where = '';
     }
     if ($params['filter']) {
         foreach ($params['filter'] as $filter) {
             $query_where .= (!empty($query_where) ? ' AND ' : ' WHERE ') . $this->_toImageDriverName($filter['property']) . ' ' . $filter['op'] . ' ' . (is_array($filter['value']) ? '(' . implode(',', $filter['value']) . ')' : $filter['value']);
         }
     }
     $sql = 'SELECT ' . $params->get('fields', 'image_id') . ' FROM ansel_images ' . $query_where . ' ORDER BY ' . $params->get('sort', 'image_sort');
     $sql = $this->_db->addLimitOffset($sql, array('limit' => $params->get('limit', 0), 'offset' => $params->get('offset', 0)));
     try {
         if ($field_count > 1) {
             $results = $this->_db->selectAll($sql);
             $images = array();
             foreach ($results as $image) {
                 $images[$image['image_id']] = $image;
             }
             return $images;
         } else {
             return $this->_db->selectValues($sql);
         }
     } catch (Horde_Db_Exception $e) {
         throw new Ansel_Exception($e);
     }
 }

 /**
  * Return a list of users who have shares with the given permissions
  * for the current user.
  *
  * @param integer $perm       The level of permissions required.
  * @param mixed  $parent      The parent share to start looking in.
  *                            (Horde_Share_Object, share_id, or null)
  * @param boolean $allLevels  Return all levels, or just the direct
  *                            children of $parent? Defaults to all levels.
  * @param integer $from       The user to start listing at.
  * @param integer $count      The number of users to return.
  *
  * @return array  List of users.
  * @throws Horde_Share_Exception
  */
 public function listOwners($perm = Horde_Perms::SHOW, $parent = null, $allLevels = true, $from = 0, $count = 0)
 {
     $sql = 'SELECT DISTINCT(s.share_owner) ' . $this->getShareCriteria($this->_user, $perm, null, $parent, $allLevels);
     if ($count) {
         $sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
     }
     try {
         $allowners = $this->_db->selectValues($sql);
     } catch (Horde_Db_Exception $e) {
         throw new Horde_Share_Exception($e);
     }
     $owners = array();
     foreach ($allowners as $owner) {
         if ($this->countShares($this->_user, $perm, $owner, $parent, $allLevels)) {
             $owners[] = $owner;
         }
     }
     return $owners;
 }

 /**
  * Retrieves a set of pages matching an SQL WHERE clause.
  *
  * @param string $table        Table to retrieve pages from.
  * @param array|string $where  Where clause for sql statement (without the
  *                             'WHERE'). If an array the 1st element is the
  *                             clause with placeholder, the 2nd element the
  *                             values.
  * @param string $orderBy      Order results by this column.
  * @param integer $limit       Maximum number of pages to fetch.
  *
  * @return array  A list of page hashes.
  * @throws Wicked_Exception
  */
 protected function _retrieve($table, $where, $orderBy = null, $limit = null)
 {
     $query = 'SELECT * FROM ' . $table;
     $values = array();
     if (!empty($where)) {
         $query .= ' WHERE ';
         if (is_array($where)) {
             $query .= $where[0];
             $values = $where[1];
         } else {
             $query .= $where;
         }
     }
     if (!empty($orderBy)) {
         $query .= ' ORDER BY ' . $orderBy;
     }
     if (!empty($limit)) {
         try {
             $query = $this->_db->addLimitOffset($query, array('limit' => $limit));
         } catch (Horde_Db_Exception $e) {
             throw new Wicked_Exception($e);
         }
     }
     try {
         $result = $this->_db->select($query, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Wicked_Exception($e);
     }
     $pages = array();
     foreach ($result as $row) {
         if (isset($row['page_name'])) {
             $row['page_name'] = $this->_convertFromDriver($row['page_name']);
         }
         if (isset($row['page_text'])) {
             $row['page_text'] = $this->_convertFromDriver($row['page_text']);
         }
         if (isset($row['change_log'])) {
             $row['change_log'] = $this->_convertFromDriver($row['change_log']);
         }
         $pages[] = $row;
     }
     return $pages;
 }

 /**
  * Searches forums for matching threads or posts.
  *
  * @param array $filter  Hash of filter criteria:
  *          'forums'         => Array of forum IDs to search.  If not
  *                              present, searches all forums.
  *          'keywords'       => Array of keywords to search for.  If not
  *                              present, finds all posts/threads.
  *          'allkeywords'    => Boolean specifying whether to find all
  *                              keywords; otherwise, wants any keyword.
  *                              False if not supplied.
  *          'message_author' => Name of author to find posts by.  If not
  *                              present, any author.
  *          'searchsubjects' => Boolean specifying whether to search
  *                              subjects.  True if not supplied.
  *          'searchcontents' => Boolean specifying whether to search
  *                              post contents.  False if not supplied.
  * @param string  $sort_by       The column by which to sort.
  * @param integer $sort_dir      The direction by which to sort:
  *                                   0 - ascending
  *                                   1 - descending
  * @param string  $from          The thread to start listing at.
  * @param string  $count         The number of threads to return.
  *
  * @return array  A search result hash where:
  *          'results'        => Array of messages.
  *          'total           => Total message number.
  * @throws Agora_Exception
  */
 public function search($filter, $sort_by = 'message_subject', $sort_dir = 0, $from = 0, $count = 0)
 {
     if (!isset($filter['allkeywords'])) {
         $filter['allkeywords'] = false;
     }
     if (!isset($filter['searchsubjects'])) {
         $filter['searchsubjects'] = true;
     }
     if (!isset($filter['searchcontents'])) {
         $filter['searchcontents'] = false;
     }
     /* Select forums ids to search in */
     $sql = 'SELECT forum_id, forum_name FROM ' . $this->_forums_table . ' WHERE ';
     if (empty($filter['forums'])) {
         $sql .= ' active = ? AND scope = ?';
         $values = array(1, $this->_scope);
     } else {
         $sql .= ' forum_id IN (' . implode(',', $filter['forums']) . ')';
         $values = array();
     }
     try {
         $forums = $this->_db->selectAssoc($sql, $values);
     } catch (Horde_Db_Exception $e) {
         throw new Agora_Exception($e->getMessage());
     }
     /* Build query  */
     $sql = ' FROM ' . $this->_threads_table . ' WHERE forum_id IN (' . implode(',', array_keys($forums)) . ')';
     if (!empty($filter['keywords'])) {
         $sql .= ' AND (';
         if ($filter['searchsubjects']) {
             $keywords = '';
             foreach ($filter['keywords'] as $keyword) {
                 if (!empty($keywords)) {
                     $keywords .= $filter['allkeywords'] ? ' AND ' : ' OR ';
                 }
                 $keywords .= 'message_subject LIKE ' . $this->_db->quote('%' . $keyword . '%');
             }
             $sql .= '(' . $keywords . ')';
         }
         if ($filter['searchcontents']) {
             if ($filter['searchsubjects']) {
                 $sql .= ' OR ';
             }
             $keywords = '';
             foreach ($filter['keywords'] as $keyword) {
                 if (!empty($keywords)) {
                     $keywords .= $filter['allkeywords'] ? ' AND ' : ' OR ';
                 }
                 $keywords .= 'body LIKE ' . $this->_db->quote('%' . $keyword . '%');
             }
             $sql .= '(' . $keywords . ')';
         }
         $sql .= ')';
     }
     if (!empty($filter['author'])) {
         $sql .= ' AND message_author = ' . $this->_db->quote(Horde_String::lower($filter['author']));
     }
     /* Sort by result column. */
     $sql .= ' ORDER BY ' . $sort_by . ' ' . ($sort_dir ? 'DESC' : 'ASC');
     /* Slice directly in DB. */
     if ($count) {
         $total = $this->_db->selectValue('SELECT COUNT(*) ' . $sql);
         $sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
     }
     $sql = 'SELECT message_id, forum_id, message_subject, message_author, message_timestamp ' . $sql;
     try {
         $messages = $this->_db->select($sql);
     } catch (Horde_Db_Exception $e) {
         throw new Agora_Exception($e->getMessage());
     }
     if (empty($messages)) {
//.........这里部分代码省略.........

 /**
  * Appends +LIMIT+ and +OFFSET+ options to a SQL statement.
  *
  * @param string $sql     SQL statement.
  * @param array $options  TODO
  *
  * @return string
  */
 public function addLimitOffset($sql, $options)
 {
     $result = $this->_read->addLimitOffset($sql, $options);
     $this->_lastQuery = $this->_write->getLastQuery();
     return $result;
 }