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;
}