/**
  *
  * @param int $days_threshold days threshold
  * @param int $ca_threshold turnover threshold
  * @param int $limit
  * @return type
  */
 public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
 {
     $akilia2db = $this->configuration['synchronizer']['db_akilia2'];
     $select = new Select();
     $bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
     $bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
     $bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
     $bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
     $so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
     $sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
     $select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
     $columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
     $select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
     $select->group($columns);
     $select->having("sum(sol.price_total_net) > {$ca_threshold}");
     $select->where(function (Where $where) use($min_accuracy) {
         //$where->greaterThan('so.date_order', '2012-12-31');
         $where->notLike('bc.name', '%FINISHED%');
         $where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
     });
     $select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
     if ($limit > 0) {
         $select->limit($limit);
     }
     $store = $this->getStore($select);
     $data = $store->getData()->toArray();
     return $data;
 }
Ejemplo n.º 2
0
 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);
 }
Ejemplo n.º 3
0
 function fetchAll($beginning, $limit)
 {
     $select = new Select($this->tableGateway->getTable());
     $select->limit($limit)->offset($beginning);
     $rowSet = $this->tableGateway->selectWith($select);
     return $rowSet;
 }
 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;
 }
Ejemplo n.º 5
0
 protected function _applyLimit()
 {
     if ($this->_limit !== false) {
         $this->_select->limit($this->_limit);
     }
     return $this;
 }
 public function getFeedByHandleAndType($handle, $type)
 {
     $select = new Select($this->getTable());
     $select->limit(1);
     $select->where->equalTo('name', $handle)->equalTo('type', $type);
     $rowset = $this->selectWith($select);
     return $rowset->current();
 }
Ejemplo n.º 7
0
 /**
  * (non-PHPdoc)
  *
  * @see \MwAdmin\Service\DataTableProviderInterface::getDataTableSelect()
  */
 public function getDataTableSelect($page = 1, $perPage = 10)
 {
     $table = $this->getDataTableGateway();
     $sql = new Select($table->getTable());
     $sql->order($this->getDefaultSort());
     $sql->limit($perPage, $page * $perPage - $perPage);
     return $sql;
 }
Ejemplo n.º 8
0
 public function getListingById($id = 1)
 {
     $select = new Select();
     $select->from($this->tableName);
     $where = new Where();
     $where->equalTo('listings_id', $id);
     $select->where($where);
     $select->limit(1);
     return $this->selectWith($select)->current();
 }
 public function fetchByUserAndId($user_id, $id)
 {
     $select = new Select($this->table);
     $select->limit(1);
     $select->where->equalTo('id', $id)->equalTo('user', $user_id);
     $bookmarks = $this->selectWith($select)->current();
     if ($bookmarks) {
         $bookmarks = $bookmarks->toArray();
     }
     return $bookmarks;
 }
 public function fetchByCollectionAndName($collection, $name)
 {
     $select = new Select($this->table);
     $select->limit(1);
     $select->where->equalTo('collection', $collection)->equalTo('name', $name);
     $rowset = $this->selectWith($select);
     $result = $rowset->current();
     if (false === $result) {
         throw new \Exception('Required `directus_setting` with collection `' . $collection . '` and name `' . $name . '` not found.');
     }
     return $result;
 }
Ejemplo n.º 11
0
 public function getlistnews($limit)
 {
     $select = new Select('catelog');
     $select->order('id DESC');
     $select->limit($limit);
     $resultSet = $this->tableGateway->selectWith($select);
     $array = array();
     foreach ($resultSet as $value) {
         array_push($array, $value);
     }
     return $array;
 }
Ejemplo n.º 12
0
 /**
  * Return all values from core_config_data
  *
  * @param string  $locale Locale
  * @param integer $limit  Limit
  *
  * @return array
  */
 public function getValues($locale = null, $limit = null)
 {
     $select = new Select();
     $select->from('core_translate')->columns(array('src_id' => 'id', 'source'))->join('core_translate_locale', 'core_translate.id = core_translate_locale.core_translate_id', array('dst_id' => 'id', 'destination', 'locale'), Select::JOIN_INNER);
     if (!empty($locale)) {
         $select->where->equalTo('core_translate_locale.locale', $locale);
     }
     if (!empty($limit)) {
         $select->limit($limit);
     }
     $select->order('core_translate.source ASC');
     return $this->fetchAll($select);
 }
 public function fetchOneById($id)
 {
     $select = new Select(self::$_tableName);
     $select->limit(1);
     $select->where->equalTo('id', $id);
     $row = $this->selectWith($select)->current();
     if (!$row) {
         return false;
     }
     $row = $row->toArray();
     // The adapter's `params` column is JSON serialized.
     $row['params'] = $this->jsonDecodeIfPossible($row['params']);
     return $row;
 }
Ejemplo n.º 14
0
 public function getLastPhotoByAlbumId($id)
 {
     $id = (int) $id;
     $select = new Select('photo');
     //CHANGE TABLE_NAME as per needs
     $select->where('album_id = ' . $id);
     $select->order('id DESC');
     $select->limit(1);
     $rowset = $this->tableGateway->selectWith($select);
     //Will get array of rows.
     $row = $rowset->current();
     if (!$row) {
         return [];
     }
     return $row;
 }
 /**
  * @param Zend\Db\Sql\Select $select
  * @return Zend\Db\ResultSet
  */
 public function performSelect(Select $select, $order, $paginated = false, $limit = null)
 {
     if ($order) {
         $select->order($order);
     }
     if ($limit) {
         $select->limit($limit);
     }
     if ($paginated) {
         $adapter = new DbSelect($select, $this->tableGateway->getAdapter(), $this->resultSetPrototype);
         $paginator = new Paginator($adapter);
         return $paginator;
     } else {
         return $this->tableGateway->selectWith($select);
     }
 }
 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);
 }
Ejemplo n.º 17
0
 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();
 }
Ejemplo n.º 18
0
 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();
 }
 public function fetchAllByGroup($groupId)
 {
     $select = new Select($this->getTable());
     $select->limit(1);
     $select->where->equalTo('group_id', $groupId);
     $row = $this->selectWith($select)->toArray();
     if (!$row) {
         return [];
     }
     $row = $row[0];
     if (array_key_exists('nav_override', $row)) {
         if (!empty($row['nav_override'])) {
             $row['nav_override'] = @json_decode($row['nav_override']);
             if (json_last_error() !== JSON_ERROR_NONE) {
                 $row['nav_override'] = false;
             }
         } else {
             $row['nav_override'] = NULL;
         }
     }
     return $row;
 }
Ejemplo n.º 20
0
 /**
  * @param Select $select
  * @param $count
  * @param $offset
  * @return Select
  */
 public function setLimit(Select $select, $count, $offset)
 {
     if ($count === null) {
         return $select;
     }
     return $select->limit($count)->offset($offset);
 }
 public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
 {
     $tableName = $this->getTable();
     if (isset($params['group_by'])) {
         $select->group($tableName . '.' . $params['group_by']);
     } else {
         $select->group($tableName . '.' . $this->primaryKeyFieldName);
     }
     //If this is a relational order, than it is an array.
     if (is_array($params['orderBy'])) {
         $select->join(array('jsort' => $params['orderBy']['junction_table']), 'jsort.' . $params['orderBy']['jkeyRight'] . ' = ' . $tableName . '.' . $this->primaryKeyFieldName, array(), $select::JOIN_LEFT);
         $select->join(array('rsort' => $params['orderBy']['related_table']), 'rsort.id = jsort.' . $params['orderBy']['jkeyLeft'], array(), $select::JOIN_LEFT);
         $select->order('rsort.title', $params['orderDirection']);
     } else {
         $select->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
     }
     if (isset($params['perPage']) && isset($params['currentPage'])) {
         $select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
     }
     // Are we sorting on a relationship?
     foreach ($schema as $column) {
         if ($column['column_name'] != $params['orderBy']) {
             continue;
         }
         // Must have defined table_related
         if (!isset($column['relationship']) || !is_array($column['relationship']) || !isset($column['relationship']['table_related'])) {
             break;
         }
         // Must have defined visible_column
         if (!isset($column['options']) || !is_array($column['options']) || !isset($column['options']['visible_column'])) {
             break;
         }
         $relatedTable = $column['relationship']['table_related'];
         $visibleColumn = $column['options']['visible_column'];
         $keyLeft = $params['table_name'] . "." . $params['orderBy'];
         // @todo it's wrong to assume PKs are "id" but this is currently endemic to directus6
         $keyRight = $relatedTable . ".id";
         $joinedSortColumn = $relatedTable . "." . $visibleColumn;
         $select->reset(Select::ORDER)->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT)->order("{$joinedSortColumn} " . $params['orderDirection']);
         break;
     }
     // Note: be sure to explicitly check for null, because the value may be
     // '0' or 0, which is meaningful.
     if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
         $haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
         if (!isset($params['table_name']) || empty($params['table_name'])) {
             $tableName = $this->getTable();
         } else {
             $tableName = $params['table_name'];
         }
         $select->where->in($tableName . '.' . STATUS_COLUMN_NAME, $haystack);
     }
     // Select only ids from the ids if provided
     if (array_key_exists('ids', $params)) {
         $entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
         if (count($entriesIds) > 0) {
             $select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
         }
     }
     // Where
     $select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($tableName . '.' . $this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
     // very very rudimentary ability to supply where conditions to fetch...
     // at the moment, only 'equalTo' and 'between' are supported... also, the 'total' key returned
     // in the json does not reflect these filters...
     // -MG
     if (array_key_exists('where', $params)) {
         $outer = $select->where->nest;
         foreach ($params['where'] as $whereCond) {
             $type = $whereCond['type'];
             $column = $whereCond['column'];
             if ($type == 'equalTo') {
                 $val = $whereCond['val'];
                 if (is_array($val)) {
                     $where = $select->where->nest;
                     foreach ($val as $currentval) {
                         $where->equalTo($column, $currentval);
                         if ($currentval != end($val)) {
                             $where->or;
                         }
                     }
                     $where->unnest;
                 } else {
                     $outer->equalTo($column, $val);
                 }
             } else {
                 if ($type == 'between') {
                     $val1 = $whereCond['val1'];
                     $val2 = $whereCond['val2'];
                     $outer->between($column, $val1, $val2);
                 }
             }
         }
         $outer->unnest;
     }
     //@TODO: Make this better
     if (isset($params['adv_where'])) {
         $select->where($params['adv_where']);
     }
     if (isset($params['adv_search']) && !empty($params['adv_search'])) {
         $i = 0;
         foreach ($params['adv_search'] as $search_col) {
             $target = array();
             foreach ($schema as $col) {
                 if ($col['id'] == $search_col['id']) {
                     $target = $col;
                     break;
                 }
             }
             if (empty($target)) {
                 continue;
             }
             // TODO: fix this, it must be refactored
             if (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOMANY") {
                 $relatedTable = $target['relationship']['table_related'];
                 $relatedAliasName = $relatedTable . "_" . $i;
                 if ($target['relationship']['type'] == "MANYTOMANY") {
                     $junctionTable = $target['relationship']['junction_table'];
                     $jkl = $target['relationship']['junction_key_left'];
                     $jkr = $target['relationship']['junction_key_right'];
                     $keyleft = $params['table_name'] . ".id";
                     $keyRight = $junctionTable . '.' . $jkl;
                     $jkeyleft = $junctionTable . '.' . $jkr;
                     $jkeyright = $relatedAliasName . ".id";
                     $select->join($junctionTable, "{$keyleft} = {$keyRight}", array(), Select::JOIN_INNER);
                 } else {
                     $select->join(array($relatedAliasName => $relatedTable), $tableName . '.' . $target['column_name'] . " = " . $relatedAliasName . ".id", array(), Select::JOIN_INNER);
                 }
                 $relatedTableMetadata = TableSchema::getSchemaArray($relatedTable);
                 if ($search_col['type'] == "like") {
                     $select->join(array($relatedAliasName => $relatedTable), "{$jkeyleft} = {$jkeyright}", array(), Select::JOIN_INNER);
                     $search_col['value'] = "%" . $search_col['value'] . "%";
                     if (isset($target['options']['filter_column'])) {
                         $targetCol = $target['options']['filter_column'];
                     } else {
                         $targetCol = $target['options']['visible_column'];
                     }
                     foreach ($relatedTableMetadata as $col) {
                         if ($col['id'] == $targetCol) {
                             if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
                                 $where = $select->where->nest;
                                 $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
                                 $columnName = $relatedAliasName . "." . $columnName;
                                 $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", $search_col['value']);
                                 $where->addPredicate($like, Predicate\Predicate::OP_OR);
                                 $where->unnest;
                             }
                         }
                     }
                 } else {
                     $select->where($jkeyleft . ' = ' . $this->adapter->platform->quoteValue($search_col['value']));
                 }
             } elseif (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOONE") {
                 $relatedTable = $target['relationship']['table_related'];
                 $keyLeft = $this->getTable() . "." . $target['relationship']['junction_key_left'];
                 $keyRight = $relatedTable . ".id";
                 $filterColumn = $target['options']['filter_column'];
                 $joinedFilterColumn = $relatedTable . "." . $filterColumn;
                 // do not let join this table twice
                 // TODO: do a extra checking in case it's being used twice
                 // and none for sorting
                 if ($target['column_name'] != $params['orderBy']) {
                     $select->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT);
                 }
                 if ($search_col['type'] == 'like') {
                     $searchLike = '%' . $search_col['value'] . '%';
                     $spec = function (Where $where) use($joinedFilterColumn, $searchLike) {
                         $where->like($joinedFilterColumn, $searchLike);
                     };
                     $select->where($spec);
                 } else {
                     $select->where($search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
                 }
             } else {
                 if ($target['type'] == "DATETIME" && strpos($search_col['value'], " ") == false) {
                     $select->where('date(' . $tableName . '.' . $search_col['id'] . ") = " . $this->adapter->platform->quoteValue($search_col['value']));
                 } else {
                     if ($search_col['type'] == "like") {
                         $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue("%" . $search_col['value'] . "%"));
                     } else {
                         $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
                     }
                 }
             }
             $i++;
         }
     } else {
         if (isset($params['search']) && !empty($params['search'])) {
             $params['search'] = "%" . $params['search'] . "%";
             $where = $select->where->nest;
             foreach ($schema as $col) {
                 if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
                     $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
                     $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
                     $where->addPredicate($like, Predicate\Predicate::OP_OR);
                 }
             }
             $where->unnest;
         }
     }
     return $select;
 }
Ejemplo n.º 22
0
 /**
  * 根据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();
 }
Ejemplo n.º 23
0
 /**
  *
  * @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;
 }
Ejemplo n.º 24
0
 public function selectOne(Select $select, ResultSet\ResultSetInterface $resultSet = null)
 {
     $select->limit(1);
     //var_dump($select->getSqlString()); die();
     return $this->select($select, $resultSet)->current();
 }
 public function fetchByUserAndTable($user_id, $table)
 {
     $select = new Select($this->table);
     $select->limit(1);
     $select->where->equalTo('table_name', $table)->equalTo('user', $user_id);
     $preferences = $this->selectWith($select)->current();
     if (!$preferences) {
         return $this->constructPreferences($user_id, $table);
     }
     if ($preferences) {
         $preferences = $preferences->toArray();
     }
     return $preferences;
 }
Ejemplo n.º 26
0
 /**
  * @param int $limit
  * @return $this
  */
 public function limit($limit)
 {
     $this->select->limit($limit);
     return $this;
 }
Ejemplo n.º 27
0
 public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
 {
     $select->group('id')->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
     if (isset($params['perPage']) && isset($params['currentPage'])) {
         $select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
     }
     // Note: be sure to explicitly check for null, because the value may be
     // '0' or 0, which is meaningful.
     if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
         $haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
         $select->where->in(STATUS_COLUMN_NAME, $haystack);
     }
     // Select only ids from the ids if provided
     if (array_key_exists('ids', $params)) {
         $entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
         if (count($entriesIds) > 0) {
             $select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
         }
     }
     // Where
     $select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
     if (isset($params['adv_search']) && !empty($params['adv_search'])) {
         $select->where($params['adv_search']);
     } else {
         if (isset($params['search']) && !empty($params['search'])) {
             $params['search'] = "%" . $params['search'] . "%";
             $where = $select->where->nest;
             foreach ($schema as $col) {
                 if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
                     $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
                     $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
                     $where->addPredicate($like, Predicate\Predicate::OP_OR);
                 }
             }
             $where->unnest;
         }
     }
     return $select;
 }
Ejemplo n.º 28
0
/**
 * 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
}
Ejemplo n.º 29
0
 /**
  * @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));
 }
Ejemplo n.º 30
0
 /**
  * {@inheritdoc}
  */
 public function limit($limit)
 {
     parent::limit((int) $limit);
     return $this;
 }