protected function _init($itemClass, $keyField, $tableName = '') { parent::_init($itemClass, $keyField); $this->_table = $tableName; $this->_select = new Select(); $this->_select->from(array('main_table' => $tableName)); }
/** * @param string $entityName */ public function __construct($entityName, Config $config, Select $select = null) { $this->entityName = $entityName; $this->config = $config; if ($select === null) { $this->select = new Select(); $this->select->from($this->config->getTable($this->entityName)); } else { $this->select = $select; } }
/** * Méthode get : récupère un ou plusieurs pays * * @param string $code * @return array * @throws \Exception */ public function getPays($code = "", $returnObject = false) { $select = new Select(); $where = null; $result = []; try { $select->from('pays'); if ($code) { $where = $this->constructWhereFromCode($code); $select->where($where); } $rowset = $this->tableGateway->selectWith($select); if ($rowset->count() > 1) { for ($i = 0; ($row = $rowset->current()) && $i < 30; $rowset->next(), $i++) { $result[] = $row->toArray(); } } else { if ($rowset->count() > 0) { if ($returnObject) { $result[] = $rowset->current(); } else { $result[] = $rowset->current()->toArray(); } } } if (empty($result)) { throw new \Exception("Nous n'avons pas trouve le pays possedant ce code, alpha2 ou alpha3 egal {$code}"); } } catch (\Exception $e) { $result['error'] = $e->getMessage(); } return $result; }
public function getBlogList() { $select = new Select(); $select->from($this->table); $select->order('date DESC'); return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype)); }
/** * Data provider for testGetSqlString * * @return array */ public function dataProvider() { $select0 = new Select(); $select0->from(array('x' => 'foo')); $expectedSql0 = 'SELECT "x".* FROM "foo" "x"'; return array(array($select0, $expectedSql0)); }
public function fetchByIdCategoryWithLimit($idCategory, $limit) { $select = new Select(); $select->from('post')->where('id_category = ' . (int) $idCategory)->limit((int) $limit); $resultSet = $this->tableGateway->select($select); return $resultSet; }
/** * Get properties * * @param boolean $forceReload to initiliaze properties * * @return array */ public function getProperties($forceReload = false) { if ($this->getData('properties') == null or $forceReload) { $select = new Select(); $select->from('tab')->columns(array())->join('property', 'tab.id = property.tab_id', '*', Select::JOIN_INNER); if ($this->getDocumentId() !== null) { $select->join('document', 'document.document_type_id = tab.document_type_id', array(), Select::JOIN_INNER); $select->join('property_value', 'document.id = property_value.document_id AND property.id = property_value.property_id', array('value'), Select::JOIN_LEFT); $select->where(array('document.id' => $this->getDocumentId())); } if ($this->getTabId() != null) { $select->where(array('tab.id' => $this->getTabId())); } if ($this->getDocumentTypeId() != null) { $select->where(array('tab.document_type_id' => $this->getDocumentTypeId())); } $select->order('property.sort_order ASC'); $rows = $this->fetchAll($select); $properties = array(); foreach ($rows as $row) { $propertyModel = Model::fromArray((array) $row); if ($this->getDocumentId() !== null) { $propertyModel->setDocumentId($this->getDocumentId()); } $properties[] = $propertyModel; } $this->setData('properties', $properties); } return $this->getData('properties'); }
/** * * @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; }
public function dataProvider() { $select0 = new Select(); $select0->from('foo')->columns(array('bar', 'baz'))->order('bar')->limit(5)->offset(10); $expectedPrepareSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?'; $expectedParams0 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10); $expectedSql0 = 'SELECT [bar], [baz] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [baz], ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10'; $expectedFormatParamCount0 = 3; $select1 = new Select(); $select1->from('foo')->columns(array('bar', 'bam' => 'baz'))->limit(5)->offset(10); $expectedPrepareSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?'; $expectedParams1 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10); $expectedSql1 = 'SELECT [bar], [bam] FROM ( SELECT [foo].[bar] AS [bar], [foo].[baz] AS [bam], ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10'; $expectedFormatParamCount1 = 3; $select2 = new Select(); $select2->from('foo')->order('bar')->limit(5)->offset(10); $expectedPrepareSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?'; $expectedParams2 = array('offset' => 10, 'limit' => 5, 'offsetForSum' => 10); $expectedSql2 = 'SELECT * FROM ( SELECT [foo].*, ROW_NUMBER() OVER (ORDER BY [bar] ASC) AS [__ZEND_ROW_NUMBER] FROM [foo] ) AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN 10+1 AND 5+10'; $expectedFormatParamCount2 = 3; $select3 = new Select(); $select3->from('foo'); $expectedPrepareSql3 = 'SELECT [foo].* FROM [foo]'; $expectedParams3 = array(); $expectedSql3 = 'SELECT [foo].* FROM [foo]'; $expectedFormatParamCount3 = 0; return array(array($select0, $expectedPrepareSql0, $expectedParams0, $expectedSql0, $expectedFormatParamCount0), array($select1, $expectedPrepareSql1, $expectedParams1, $expectedSql1, $expectedFormatParamCount1), array($select2, $expectedPrepareSql2, $expectedParams2, $expectedSql2, $expectedFormatParamCount2), array($select3, $expectedPrepareSql3, $expectedParams3, $expectedSql3, $expectedFormatParamCount3)); }
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); }
/** * {@inheritDoc} */ public function getRoles() { /* @var $tableGateway \Zend\Db\TableGateway\TableGateway */ $tableGateway = $this->serviceLocator->get('BjyAuthorize\\Service\\RoleDbTableGateway'); $sql = new Select(); $sql->from($this->tableName); /* @var $roles Role[] */ $roles = array(); $indexedRows = array(); $rowset = $tableGateway->selectWith($sql); // Pass 1: collect all rows and index them by PK foreach ($rowset as $row) { $indexedRows[$row[$this->identifierFieldName]] = $row; } // Pass 2: build a role for each indexed row foreach ($indexedRows as $row) { $parentRoleId = isset($row[$this->parentRoleFieldName]) ? $indexedRows[$row[$this->parentRoleFieldName]][$this->roleIdFieldName] : null; $roleId = $row[$this->roleIdFieldName]; $roles[$roleId] = new Role($roleId, $parentRoleId); } // Pass 3: Re-inject parent objects to preserve hierarchy foreach ($roles as $role) { $parentRoleObj = $role->getParent(); if ($parentRoleObj && ($parentRoleId = $parentRoleObj->getRoleId())) { $role->setParent($roles[$parentRoleId]); } } return array_values($roles); }
public function find($params) { $sql = new Sql($this->getAdapter()); $select = new Select(); $select->from($params['from']); if (!empty($params['columns'])) { $select->columns($params['columns']); } foreach ($params['where'] as $where) { $select->where($where); } foreach ($params['joins'] as $join) { if (empty($join['columns'])) { $join['columns'] = Select::SQL_STAR; } if (empty($join['type'])) { $join['type'] = Select::JOIN_INNER; } $select->join($join['name'], $join['on'], $join['columns'], $join['type']); } $query = $sql->getSqlStringForSqlObject($select); $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE); $data = $results->toArray(); if (empty($data)) { return false; } else { if (count($data) == 1) { return $data[0]; } else { return $data; } } }
public function fetchAll($paginated = false, $sort = 'name', $order = 'ASC') { switch ($sort) { case "id": $sort = "C_Id"; break; case "name": $sort = "C_Name"; break; case "lastinvoice": $sort = "C_LastInvoice"; break; default: $sort = "C_Name"; } $select = new Select(); $select->from($this->table); $subquery = "(SELECT I_BaseDate FROM Invoice WHERE Invoice.I_ClientId = Client.C_Id ORDER BY I_Id DESC LIMIT 1)"; $select->columns(array('C_Id' => 'C_Id', 'C_Name' => 'C_Name', 'C_PaymentTerms' => 'C_PaymentTerms', 'C_Reference' => 'C_Reference', 'C_CRN' => 'C_CRN', 'C_Class' => 'C_Class', 'C_Updated' => 'C_Updated', 'C_LastInvoice' => new Expression($subquery)))->order($sort . ' ' . $order); if ($paginated) { return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype)); } $resultSet = $this->select($select); return $resultSet; }
public function getBuilder() { if (empty($this->parsed['SELECT'])) { throw new \InvalidArgumentException('Queries other than SELECT are not supported yet'); } $sql = new Select(); $sql->columns($this->getBaseExprs($this->parsed['SELECT'])); foreach ($this->parsed['FROM'] as $table) { if (!$table['ref_type']) { $sql->from(array($table['alias']['name'] => $table['table'])); continue; } if ('JOIN' == $table['join_type']) { $table['join_type'] = 'INNER'; } $sql->join(array($table['alias']['name'] => $table['table']), join(' ', $this->getBaseExprs($table['ref_clause'])), array(), $table['join_type']); } if (isset($this->parsed['GROUP'])) { $sql->group($this->getBaseExprs($this->parsed['GROUP'])); } if (isset($this->parsed['WHERE'])) { $sql->where(join(' ', $this->getBaseExprs($this->parsed['WHERE']))); } unset($this->parsed['WHERE'], $this->parsed['GROUP'], $this->parsed['FROM'], $this->parsed['WHERE']); var_dump($this->parsed); #die('Stopped at ' . __FILE__ . ' on line ' . __LINE__); return $sql; }
public function buscarUm($id) { $id = (int) $id; $select = new Select(); $select->from('clientes')->columns(['id', 'nome', 'email', 'criado', 'modificado'])->where(['id' => $id]); $dados = $this->selectWith($select); return $dados->current(); }
public function findByContactId($id) { $sql = new Select(); $sql->from(array('cc' => 'contact_companies'))->join(array('c' => 'contact_company'), 'c.company_id = cc.company_id'); $where = new Where(); $where->equalTo('cc.contact_id', $id); return $this->select($sql->where($where)); }
public function fetchMoi($id) { $select = new Select(); $select->from(array('m' => 'message'))->join(array('l' => 'livre_d_or'), 'm.id_livre = l.id_livre')->join(array('u' => 'utilisateur'), 'm.id_utilisateur = u.id_utilisateur', array('posteur' => 'login_utilisateur')); $select->where('m.id_livre = "' . $id . '"'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
public function fetchDrafts() { $select = new Select(); $select->from($this->table); $select->columns(array('I_Id' => 'I_Id', 'I_Number' => 'I_Number', 'I_BaseDate' => 'I_BaseDate', 'I_Vat' => 'I_Vat', 'I_DueDays' => 'I_DueDays', 'I_SentDate' => 'I_SentDate', 'I_PayedDate' => 'I_PayedDate', 'I_ClientId' => 'I_ClientId', 'I_ClientReference' => 'I_ClientReference', 'I_OwnReference' => 'I_OwnReference', 'I_AddressId' => 'I_AddressId', 'I_Text' => 'I_Text', 'I_Updated' => 'I_Updated'))->where(array(new \Zend\Db\Sql\Predicate\IsNull('I_Number')))->join('Client', 'Invoice.I_ClientId = Client.C_Id'); $resultSet = $this->selectWith($select); return $resultSet; }
public function commandeEnCours($commande_id) { $id = (int) $commande_id; $select = new Select(); $select->from('commandeproduit')->where(array('COMMANDE_ID' => $id, 'COMMANDEPRODUIT_ACTIF' => 1)); $resultSet = $this->tableGateway->selectwith($select); return $resultSet; }
public function getAllTva() { $select = new Select(); $select->from('tva')->order('TVA_NOM')->where('TVA_ACTIF = 1'); $resultSet = $this->tableGateway->selectwith($select); $resultSet->buffer(); return $resultSet; }
public function getNewsList() { $select = new Select(); $select->columns(['content' => 'en', 'title' => 'en_title', 'url' => 'en_title', 'date']); $select->from($this->table); $select->order('date DESC'); return new \Zend\Paginator\Paginator(new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype)); }
public function getAllMenu() { $select = new Select(); $select->from('menu')->order('MENU_NOM')->where('MENU_ACTIF = 1'); $resultSet = $this->tableGateway->selectWith($select); $resultSet->buffer(); return $resultSet; }
public function findByIdAndUser($addressId, $userId) { $where = new Where(); $where->equalTo('ua.user_id', $userId)->equalTo('ua.address_id', $addressId); $sql = new Select(); $sql->from(array('ua' => 'user_addresses'))->join(array('a' => 'address'), 'ua.address_id = a.address_id')->where($where); return $this->select($sql)->current(); }
public function getAllParametre() { $select = new Select(); $select->from('PARAMETRE')->order('PARAMETRE_NOM'); $resultSet = $this->tableGateway->selectwith($select); $resultSet->buffer(); return $resultSet; }
public function findByContactId($id) { $sql = new Select(); $sql->from('contact_phone'); $where = new Where(); $where->equalTo('contact_id', $id); return $this->select($sql->where($where)); }
public function findByContactId($id) { $sql = new Select(); $sql->from(array('ca' => 'contact_addresses'))->join(array('a' => 'address'), 'a.address_id = ca.address_id'); $where = new Where(); $where->equalTo('ca.contact_id', $id); return $this->select($sql->where($where)); }
public function buscarUm($id) { $id = (int) $id; $select = new Select(); $select->from('contas')->columns(array('id', 'fk_tipo', 'fk_cliente', 'nome', 'saldo' => new Expression("FORMAT(saldo, 2, 'de_DE')"), 'criado', 'modificado'))->where(array('id' => $id)); $dados = $this->selectWith($select); return $dados->current(); }
public function fetchAll() { $select = new Select(); $select->from(array('u' => 'users'))->join(array('a' => 'album'), 'a.id_user = u.id'); $select->where('u.user_name = "' . $this->identity . '"'); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; }
public function findByItemId($itemId) { $select = new Select(); $select->from('cart_item_index'); $where = new Where(); $where->equalTo('item_id', $itemId); $resultSet = $this->selectMany($select->where($where)); return $resultSet; }
public function getAllTypeproduit() { $select = new Select(); // $where = new Where(); $select->from('TYPEPRODUIT')->join(array('t' => 'TVA'), 'TYPEPRODUIT.TVA_ID = t.TVA_ID')->columns(array('TVA_NOM'))->where(array('TYPEPRODUIT.TVA_ID' => 'TVA.TVA_NOM')); $resultSet = $this->tableGateway->selectwith($select); $resultSet->buffer(); return $resultSet; }