public function testAddCategoryAsTopLevelCategoryCreatesLinker()
{
$mapper = $this->getMapper();
$mapper->addCategory(null, 6);
$select = new \Zend\Db\Sql\Select('catalog_category_website');
$select->where(array('category_id' => 6));
$testMapper = $this->getTestMapper();
$result = $testMapper->query($select);
$this->assertTrue(is_array($result));
}
public function index03Action()
{
$adapter = $this->getServiceLocator()->get("db_books");
$selectObj = new \Zend\Db\Sql\Select("user");
$selectObj->columns(array("id", "name"))->order(array("id DESC"));
$adapterPaginator = new \Zend\Paginator\Adapter\DbSelect($selectObj, $adapter);
$paginator = new \Zend\Paginator\Paginator($adapterPaginator);
$currentPage = $this->params()->fromRoute("page");
$paginator->setItemCountPerPage(3)->setCurrentPageNumber($currentPage)->setPageRange(3);
return array("paginator" => $paginator);
}
/**
* 显示所有已收集的Email地址
*/
public function indexAction()
{
$hydrator = new \Zend\Stdlib\Hydrator\ObjectProperty();
$objectPrototype = new \Console\Model\Address();
$resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $objectPrototype);
$query = new \Zend\Db\Sql\Select();
$query->from('address');
$adapter = new \Zend\Paginator\Adapter\DbSelect($query, $this->dbAdapter, $resultSet);
$paginator = new \Zend\Paginator\Paginator($adapter);
$paginator->setCurrentPageNumber($this->params()->fromRoute('page'));
return new ViewModel(array('paginator' => $paginator));
}
public function getDocumentPaginator($page_num)
{
$hydrator = new ObjectPropertyHydrator();
$objectPrototype = new Document();
$resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $objectPrototype);
$query = new \Zend\Db\Sql\Select();
$query->from('document');
$adapter = new \Zend\Paginator\Adapter\DbSelect($query, $this->adapter, $resultSet);
$paginator = new \Zend\Paginator\Paginator($adapter);
$paginator->setCurrentPageNumber($page_num);
return $paginator;
}
public function testAddOptionCreatesLinker()
{
$choiceId = $this->insertChoice(1);
$optionId = $this->insertOption();
$mapper = $this->getMapper();
$mapper->addOption($choiceId, $optionId);
$select = new \Zend\Db\Sql\Select('catalog_choice_option');
$select->where(array('option_id' => $optionId, 'choice_id' => $choiceId));
$testMapper = $this->getTestMapper();
$result = $testMapper->query($select);
$this->assertTrue(is_array($result));
}
public function getQueue($task_id, $page_num)
{
$hydrator = new ObjectPropertyHydrator();
$objectPrototype = new Queue();
$resultSet = new \Zend\Db\ResultSet\HydratingResultSet($hydrator, $objectPrototype);
$query = new \Zend\Db\Sql\Select();
$query->from('queue')->where(array('task_id' => $task_id));
$adapter = new \Zend\Paginator\Adapter\DbSelect($query, $this->adapter, $resultSet);
$paginator = new \Zend\Paginator\Paginator($adapter);
$paginator->setCurrentPageNumber($page_num);
$paginator->setDefaultItemCountPerPage(100);
return $paginator;
}
public function withdrawAction()
{
$this->appendTitle($this->translate('Withdraw list'));
$dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$select = new \Zend\Db\Sql\Select();
$select->from('withdraw')->order(array('create_time' => 'desc'));
$dbselect = new DbSelect($select, $dbAdapter);
$paginator = new Paginator($dbselect);
$paginator->setCurrentPageNumber($this->params()->fromRoute('page'));
$vars = array('paginator' => $paginator, 'sl' => $this->serviceLocator);
$view_page = new ViewModel($vars);
$view_page = $this->setChildViews($view_page);
return $view_page;
}
/**
* The default action - show the home page
*/
public function indexAction()
{
$this->appendTitle($this->translate('Trade query'));
$dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
$select = new \Zend\Db\Sql\Select();
$select->from('trade')->where(array('merchant_id' => $this->getMerchantId()))->order(array('create_time' => 'desc'));
$dbselect = new DbSelect($select, $dbAdapter);
$paginator = new Paginator($dbselect);
$paginator->setCurrentPageNumber($this->params()->fromRoute('page'));
$vars = array('paginator' => $paginator);
$view_page = new ViewModel($vars);
$view_page = $this->setChildViews($view_page);
return $view_page;
}
public function testSortOptionsChangesOrderOfOptions()
{
$table = 'catalog_product_option';
$productId = 1;
$linker1 = array('product_id' => $productId, 'option_id' => 3, 'sort_weight' => 0);
$linker2 = array('product_id' => $productId, 'option_id' => 4, 'sort_weight' => 1);
$testMapper = $this->getTestMapper();
$testMapper->insert($linker1, $table);
$testMapper->insert($linker2, $table);
$order = array(4, 3);
$mapper = $this->getMapper();
$mapper->sortOptions($productId, $order);
$select = new \Zend\Db\Sql\Select($table);
$select->where(array('option_id' => 4, 'sort_weight' => 0));
$result = $testMapper->query($select);
$this->assertTrue(is_array($result));
}
public function testSortChoicesChangesOrderOfChoices()
{
$optionId = 1;
$choice1 = array('option_id' => $optionId, 'sort_weight' => 0);
$choice2 = array('option_id' => $optionId, 'sort_weight' => 1);
$testMapper = $this->getTestMapper();
$choiceId1 = $testMapper->insert($choice1, 'catalog_choice')->getGeneratedValue();
$choiceId2 = $testMapper->insert($choice2, 'catalog_choice')->getGeneratedValue();
$order = array($choiceId2, $choiceId1);
$mapper = $this->getMapper();
$mapper->sortChoices($optionId, $order);
$select = new \Zend\Db\Sql\Select('catalog_choice');
$select->where(array('choice_id' => $choiceId2));
$result = $testMapper->query($select);
$this->assertTrue(is_array($result));
$this->assertTrue($result['sort_weight'] == 0);
}
/**
* Reset the instance of select that is used in the select method if part is not set
* Reset the defined part in the select if part is set
*
* @param string $part
*/
public function reset($part = null)
{
if ($part) {
$this->select->reset($part);
} else {
$this->select = null;
}
return $this;
}
public function fetchAll($where = '', $order = '', $limit = '', $paginated = '')
{
if ($paginated) {
$select = new \Zend\Db\Sql\Select('hwi_product');
if (is_array($where)) {
$select->where($where);
}
if (is_array($order)) {
foreach ($order as $key => $value) {
$select->order($value);
}
}
$rs = new \Zend\Db\ResultSet\ResultSet();
$rs->setArrayObjectPrototype(new product());
$pageAdapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->tableGateway->getAdapter(), $rs);
$paginator = new \Zend\Paginator\Paginator($pageAdapter);
return $paginator;
}
$resultSet = $this->tableGateway->select($where, $order, $limit);
return $resultSet;
}
public function getAll()
{
$select = new \Zend\Db\Sql\Select();
$select->from($this->tableGateway->getTable());
return $select;
}
/**
* get ZF2 Zend Db Select
* @return Zend\Db\Sql\Select
*/
protected function getDbSelect($isSetDefaultValue = true)
{
$select = new Zend\Db\Sql\Select();
if ($isSetDefaultValue) {
$select->columns(array($this->pk));
$select->from($this->tableName);
}
return $select;
}
public static function ListaWhere($nazwaSlownika, $kolumna, $wartosc)
{
$projectTable = new TableGateway($nazwaSlownika, \Zend\Db\TableGateway\Feature\GlobalAdapterFeature::getStaticAdapter());
$select = new \Zend\Db\Sql\Select($nazwaSlownika);
$where = new Where();
$where->equalTo($kolumna, $wartosc);
$select->where($where);
$rowset = $projectTable->selectWith($select);
if ($rowset) {
return $rowset->current();
}
return array();
}
public function index10Action()
{
$adapter = $this->getServiceLocator()->get("dbConfig");
$selectObj = new \Zend\Db\Sql\Select();
$selectObj->from(array("p" => "products"))->columns(array("id", "name", "cat_id"))->order(array("cat_id ASC", "id ASC"));
$stringSql = $selectObj->getSqlString($adapter->getPlatForm());
$result = $adapter->query($stringSql)->execute();
foreach ($result as $row) {
echo "<pre style='font-weight:bold'>";
print_r($row);
echo "</pre>";
}
return false;
}
<?php
/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);
$where = new Zend\Db\Sql\Where();
$where->equalTo('id', 1)->OR->equalTo('id', 2);
$where->OR->NEST->like('name', 'Ralph%')->OR->greaterThanOrEqualTo('age', 30)->AND->lessThanOrEqualTo('age', 50)->UNNEST->literal('foo = ?', 'bar');
$target = <<<EOS
SELECT "foo".* FROM "foo" WHERE "id" = '1' OR "id" = '2' OR ("name" LIKE 'Ralph%' OR "age" >= '30' AND "age" <= '50') AND foo = 'bar'
EOS;
$select = new Zend\Db\Sql\Select('foo');
$select->where($where);
assert_example_works($target == $select->getSqlString());
/**
* Get the most searched queries
*
* @param $from begin time
* @param $to end time
*
* @return associative array
*/
public function getMostSearchedQueries($from = null, $to = null, $limit = 20)
{
$subSelect = $this->sql->select();
$subSelect->columns(array('phrase' => 'value'));
$subSelect->join(array('noresults' => 'user_stats_fields'), 'user_stats_fields.id = noresults.id', array());
$subSelect->join(array('user_stats' => 'user_stats'), 'user_stats.id = user_stats_fields.id', array('session' => 'session'));
if ($from && $to) {
$format = "Y-m-d H:i:s";
$from = date($format, $from);
$to = date($format, $to);
$subSelect->where->greaterThan('user_stats.datestamp', $from);
$subSelect->where->lessThan('user_stats.datestamp', $to);
}
$subSelect->where->equalTo('user_stats_fields.field', 'phrase');
$subSelect->where->equalTo('noresults.field', 'noresults');
$subSelect->where->EqualTo('noresults.value', 'false');
$subSelect->where->notEqualTo('user_stats_fields.value', '');
$subSelect->group(array('phrase', 'session'));
$select = new \Zend\Db\Sql\Select();
$select->columns(array('query' => 'phrase', 'count' => new Expression("COUNT(*)")));
$select->from(array('phrase' => $subSelect));
$select->group(array('phrase'));
$select->order('count desc');
$select->limit($limit);
$statement = $this->sql->prepareStatementForSqlObject($select);
$result = $statement->execute();
return $result;
}
