public function getList($where = array(), $order = null, $offset = null, $limit = null) { if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) { $select = new Select(); $select->from(array('b' => 'Product')); $select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName')); $select->where($where); $select->offset($offset); $select->limit($limit); $select->order($order); } else { $select = $this->getSelect(); $select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1)); $select->order($order); } $paginator = $this->paginate($select); $paginator->setCurrentPageNumber(ceil($offset / $limit) + 1); //$paginator->setItemCountPerPage(1); $products = $paginator->getCurrentItems()->getArrayCopy(); $pages = $paginator->getPages(); $productsCount = $paginator->getTotalItemCount(); foreach ($products as $k => $v) { $products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']); } return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages); }
public function fetchPostsByFeeds($feeds, $offset = null, $limit = null) { $select = new Select(self::$_tableName); if (!is_null($offset)) { $select->offset($offset); } if (!is_null($limit)) { $select->limit($limit); } $select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC'); $select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1); $FeedWhere = new Where(); $SocialCache = new SocialCache(); foreach ($feeds as $feed) { // Run scrape if due $SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']); $FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest; $select->where($FeedWhere); } $socialPosts = $this->selectWith($select); $socialPosts = $socialPosts->toArray(); // Unserialize cached feed entry API-responses foreach ($socialPosts as &$post) { $post['data'] = json_decode($post['data'], true); } return $socialPosts; }
public function fetchAll($columns = null, $where = null, $limit = null, $offset = null) { $select = new Select(); $select->from($this->getTable()); if ($columns) { $select->columns($columns); } if ($where) { $select->where($where); } if ($limit) { $select->limit((int) $limit); } if ($offset) { $select->offset((int) $offset); } return $this->selectWith($select); }
public function getPostsByDate($threadId, $pageNo = 1) { $selectPost = new Select(); $selectPost->from('post'); $selectPost->group('user_id'); $selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]); $predicate = new Predicate(null, Predicate::OP_AND); $predicate->equalTo('thread_id', $threadId); $select = new Select(); $select->from($this->tableName); $select->order('date_added ASC'); $select->join(['po' => $selectPost], 'post.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER); $select->join('user', 'user.user_id = post.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER); $select->limit(20); $select->offset(($pageNo - 1) * 20); $select->columns(['id', 'date_added', 'content', 'last_updated', 'username' => new Expression('user.username'), 'user_title' => new Expression('user.title'), 'user_avatar' => new Expression('user.avatar'), 'user_signature' => new Expression('user.post_signature'), 'user_joined' => new Expression('user.date_joined'), 'user_postcount' => new Expression('po.postcount')], false); $select->where($predicate); $result = $this->select($select); return $result->toArray(); }
public function getThreadsByDate($threadId, $pageNo = 1) { $selectPost = new Select(); $selectPost->join('user', 'post.user_id = user.user_id', [], $selectPost::JOIN_LEFT . ' ' . $selectPost::JOIN_OUTER); $selectPost->from('post')->group('thread_id'); $selectPost->columns(['thread_id', 'count' => new Expression('COUNT(thread_id)'), 'last_post' => new Expression('MAX(date_added)'), 'poster' => new Expression('username')]); $predicate = new Predicate(null, Predicate::OP_AND); $predicate->equalTo('forum_id', $threadId)->equalTo('deleted', '0'); $select = new Select(); $select->from($this->tableName); $select->order('date_updated DESC'); $select->limit(20); $select->join('user', 'thread.user_id = user.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER); $select->join(['po' => $selectPost], 'thread.id = po.thread_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER); $select->columns(['id', 'title', 'date_added', 'forum_id', 'view_count', 'sticky', 'post_count' => new Expression('count'), 'creator' => new Expression('username'), 'last_poster' => new Expression('poster'), 'last_post_date' => new Expression('last_post')]); $select->offset(($pageNo - 1) * 20); $select->where($predicate); $result = $this->select($select); return $result->toArray(); }
/** * @testdox unit test: Test reset() resets internal stat of Select object, based on input * @covers Zend\Db\Sql\Select::reset */ public function testReset() { $select = new Select(); // table $select->from('foo'); $this->assertEquals('foo', $select->getRawState(Select::TABLE)); $select->reset(Select::TABLE); $this->assertNull($select->getRawState(Select::TABLE)); // columns $select->columns(array('foo')); $this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS)); $select->reset(Select::COLUMNS); $this->assertEmpty($select->getRawState(Select::COLUMNS)); // joins $select->join('foo', 'id = boo'); $this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS)); $select->reset(Select::JOINS); $this->assertEmpty($select->getRawState(Select::JOINS)); // where $select->where('foo = bar'); $where1 = $select->getRawState(Select::WHERE); $this->assertEquals(1, $where1->count()); $select->reset(Select::WHERE); $where2 = $select->getRawState(Select::WHERE); $this->assertEquals(0, $where2->count()); $this->assertNotSame($where1, $where2); // group $select->group(array('foo')); $this->assertEquals(array('foo'), $select->getRawState(Select::GROUP)); $select->reset(Select::GROUP); $this->assertEmpty($select->getRawState(Select::GROUP)); // having $select->having('foo = bar'); $having1 = $select->getRawState(Select::HAVING); $this->assertEquals(1, $having1->count()); $select->reset(Select::HAVING); $having2 = $select->getRawState(Select::HAVING); $this->assertEquals(0, $having2->count()); $this->assertNotSame($having1, $having2); // limit $select->limit(5); $this->assertEquals(5, $select->getRawState(Select::LIMIT)); $select->reset(Select::LIMIT); $this->assertNull($select->getRawState(Select::LIMIT)); // offset $select->offset(10); $this->assertEquals(10, $select->getRawState(Select::OFFSET)); $select->reset(Select::OFFSET); $this->assertNull($select->getRawState(Select::OFFSET)); // order $select->order('foo asc'); $this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER)); $select->reset(Select::ORDER); $this->assertNull($select->getRawState(Select::ORDER)); }
/** * @param int $offset * @return $this */ public function offset($offset) { $this->select->offset($offset); return $this; }
/** * {@inheritdoc} */ public function offset($offset) { parent::offset((int) $offset); return $this; }
/** * Returns the option to a LIMIT SQL statement, because it varies accross DB systems * * @access public * @param int Number of the first row to return data from * @param int Number of rows to return * @return string SQL string to pass to a LIMIT statement */ function serendipity_db_limit($offset, $limit) { global $serendipity; $sql = new Sql($serendipity['dbConn']); $select = new Select(); $select->limit($limit); $select->offset($offset); return str_replace("LIMIT", "", $sql->getSqlStringForSqlObject($select)); # TODO: Find a way to not hardcode the LIMIT here }
/** * * @param Select $select * @param Options $options * @return Select */ protected function assignOptions(Select $select, Options $options) { if ($options->hasLimit()) { $select->limit($options->getLimit()); if ($options->hasOffset()) { $select->offset($options->getOffset()); } $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS')); } return $select; }
/** * 根据columns主要信息 */ function getSelectInfo($where = array(), $order = array(), $limit = '', $offset = '') { $select = new Select(); $select->from($this->getTable())->columns($this->selectColumns); empty($where) ?: $select->where($where); empty($limit) ?: $select->limit((int) $limit); empty($offset) ?: $select->offset((int) $offset); empty($order) ? $select->order(array($this->_primary => 'desc')) : $select->order($order); return $this->selectWith($select)->toArray(); }
public function fetchDisappOnlineNow() { $select = new Select("camgirls_all"); $select->where(array('unixtime_last>unix_timestamp()-300')); $select->where(array('status="disapp"')); $select->order("unixtime_last DESC"); $select->offset(0); $select->limit(20); $resultSet = $this->selectWith($select); return $this->arrWrapper($resultSet); }
protected function setSelectLimitOffset(Select $selectSQL, Query $query) { $limits = $query->getLimit(); $limit = !$limits ? self::LIMIT_INFINITY : $limits->getLimit(); $offset = !$limits ? 0 : $limits->getOffset(); if ($limit != self::LIMIT_INFINITY) { $selectSQL->limit($limit); } if ($offset != 0) { $selectSQL->offset($offset); } return $selectSQL; }
public function offset($offset) { return $this->zendSelect->offset($offset); }
protected function fetchCount(Select $select) { $select->limit(1); $select->offset(null); //NOTE: no method could reset order here //$select->order(array()); $select->reset('order'); $countColumnName = self::ROW_COUNT_COLUMN; if ($this->primaryKey && is_string($this->primaryKey)) { $select->columns(array($countColumnName => new Expression("COUNT({$this->primaryKey})"))); } else { $select->columns(array($countColumnName => new Expression('COUNT(*)'))); } //p($select->getSqlString()); $resultSet = $this->selectWith($select); if (false === $this->enableCount) { $this->lastSelectString = $select->getSqlString(); $this->reset(); } if (!$resultSet) { return 0; } $resultSet = $resultSet->current(); return $this->lastSelectCount = $resultSet->{$countColumnName}; }
public function buildSelect(array $conditions) { $select = new Select(); if (isset($conditions['from']) && $conditions['from']) { $select->from($conditions['from']); } if (isset($conditions['columns']) && $conditions['columns']) { $select->columns($conditions['columns']); } if (isset($conditions['joins']) && is_array($conditions['joins'])) { foreach ($conditions['joins'] as $join) { list($join_name, $join_on, $join_columns) = $join; if (!isset($join[3])) { $join_type = $select::JOIN_INNER; } else { switch ($join[3]) { case 'INNER': $join_type = $select::JOIN_INNER; break; case 'OUTER': $join_type = $select::JOIN_OUTER; break; case 'LEFT': $join_type = $select::JOIN_LEFT; break; case 'RIGHT': $join_type = $select::JOIN_RIGHT; break; default: $join_type = $select::JOIN_INNER; break; } } if (is_array($join_name)) { $join_table = key($join_name); } else { $join_table = $join_name; } $join_ons = new Predicate\PredicateSet(); if (is_array($join_on)) { foreach ($join_on as $p) { if ($p instanceof Predicate\PredicateInterface) { $join_ons->addPredicate($p); } else { if (is_array($p) && count($p) == 3) { $join_ons->addPredicate(new Predicate\Operator($p[0], $p[1], $p[2])); } else { if (is_string($p)) { $join_ons->addPredicate(new Predicate\Expression($p)); if (strpos($p, $join_table . ".bool_deleted") !== false) { unset($join_table); } } } } } } else { $join_ons->addPredicate(new Predicate\Expression($join_on)); if (strpos($join_on, $join_table . ".bool_deleted") !== false) { unset($join_table); } } if (isset($join_table)) { $join_ons->addPredicate(new Predicate\Expression($join_table . ".bool_deleted = '0'")); } $select->join($join_name, $join_ons, $join_columns, $join_type); } } if (isset($conditions['like']) && is_array($conditions['like'])) { foreach ($conditions['like'] as $column => $val) { $conditions['where'][] = new Predicate\Like($column, '%' . $val . '%'); } } if (isset($conditions['like_begin']) && is_array($conditions['like_begin'])) { foreach ($conditions['like_begin'] as $column => $val) { $conditions['where'][] = new Predicate\Like($column, $val . '%'); } } if (isset($conditions['like_end']) && is_array($conditions['like_end'])) { foreach ($conditions['like_end'] as $column => $val) { $conditions['where'][] = new Predicate\Like($column, '%' . $val); } } $select->where($conditions['where']); if (isset($conditions['group']) && $conditions['group']) { $select->group($conditions['group']); } if (isset($conditions['having']) && $conditions['having']) { if (is_array($conditions['having'])) { foreach ($conditions['having'] as $combination => $having) { if ($combination !== Predicate\PredicateSet::OP_OR) { $combination = Predicate\PredicateSet::OP_AND; } $select->having($having, $combination); } } else { $select->having($conditions['having']); } } if (isset($conditions['order'])) { if (is_array($conditions['order']) && count($conditions['order']) > 0) { $orderBy = array(); foreach ($conditions['order'] as $field => $sort) { if (is_int($field)) { $orderBy[] = $sort; } else { $orderBy[] = $field . ' ' . $sort; } } $select->order($orderBy); } else { if ($conditions['order']) { $select->order($conditions['order']); } } } if (isset($conditions['limit']) && $conditions['limit']) { $select->limit($conditions['limit']); } if (isset($conditions['offset']) && $conditions['offset']) { $select->offset($conditions['offset']); } //print_ar($conditions); //echo($select->getSqlString());exit; return $select; }