public function findAlbumByTest() { $sql = new \Zend\Db\Sql\Sql($this->tableGateway->getAdapter()); $select = $sql->select(); $select->from(array('a' => 'album'))->join(array('t' => 'tests'), 't.test = a.title'); $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); return $results; }
/** * @param string $email * @return \NekoPHP\Modules\User\Models\User */ public static function getByEmail($email) { $db = parent::createZendDb(); $sql = new \Zend\Db\Sql\Sql($db); $query = $sql->select()->from(parent::getTable())->columns(['id'])->where(['email' => $email]); $stmt = $sql->prepareStatementForSqlObject($query); $results = $stmt->execute(); if ($results->count() != 1) { return null; } $row = $results->next(); return new self($row['id']); }
/** * Zend db query, access * * @param Zend\Db\Sql\Select * @return statement result object */ public function query($select) { $adapter = $this->getAdapter(); $zendSql = new Zend\Db\Sql\Sql($adapter); if (isTraining()) { di('log')->sql($select->getSqlString($adapter->getPlatform())); } $this->error = null; try { $statement = $zendSql->prepareStatementForSqlObject($select); $results = $statement->execute(); } catch (Exception $e) { $this->error = $e; return false; } return $results; }
/** * @return bool */ public function update() { $id = $this->getIdentifier(); $db = $this->createZendDb(); $sql = new \Zend\Db\Sql\Sql($db); $query = $sql->update()->table(self::getTable())->set($this->fields)->where([$id['column'] => $id['value']]); try { $stmt = $sql->prepareStatementForSqlObject($query); $result = $stmt->execute(); } catch (\Exception $e) { $this->exception = $e; return false; } return true; }
/** * @param $postId int * * @return void */ public function delete($postId) { $sql = new \Zend\Db\Sql\Sql($this->adapter); $delete = $sql->delete()->from('news')->where(array('id' => $postId)); $statement = $sql->prepareStatementForSqlObject($delete); $statement->execute(); }
/** * Return clients matching criteria * * @param array $properties Properties to be returned. If empty or null, return all properties. * @param string $order Property to sort by * @param string $direction One of [asc|desc] * @param string|array $filter Name or array of names of a pre-defined filter routine * @param string|array $search Search parameter(s) passed to the filter. May be case sensitive depending on DBMS. * @param string|array $operators Comparison operator(s) * @param bool|array $invert Invert query results (return clients not matching criteria) * @param bool $addSearchColumns Add columns with search criteria (default), otherwise only columns from $properties * @param bool $distinct Force distinct results. * @param bool $query Perform query and return result set (default), return \Zend\Db\Sql\Select object otherwise. * @return \Zend\Db\ResultSet\AbstractResultSet|\Zend\Db\Sql\Select Query result or Query object * @throws \InvalidArgumentException if a filter or order column is invalid * @throws \LogicException if $invertResult is not supported by a filter or type of custom field is not supported */ public function getClients($properties = null, $order = null, $direction = 'asc', $filter = null, $search = null, $operators = null, $invert = null, $addSearchColumns = true, $distinct = false, $query = true) { $clients = $this->_serviceLocator->get('Database\\Table\\Clients'); $map = $clients->getHydrator()->getExtractorMap(); $fromClients = array(); if (empty($properties)) { $properties = array_keys($map); // Select all properties } foreach ($properties as $property) { if (isset($map[$property])) { $fromClients[] = $map[$property]; } elseif (preg_match('/^Windows\\.(.*)/', $property, $matches)) { $column = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator()->extractName($matches[1]); $fromWindows["windows_{$column}"] = $column; } // Ignore other properties. They might get added by a filter. } // add PK if not already selected if (!in_array('id', $fromClients)) { $fromClients[] = 'id'; } // Set up Select object manually instead of pulling it from a table // gateway because the base table might be changed later. $sql = new \Zend\Db\Sql\Sql($this->_serviceLocator->get('Db')); $select = $sql->select(); $select->from('clients'); $select->columns($fromClients); if ($distinct) { $select->quantifier('DISTINCT'); } if (isset($fromWindows)) { // Use left join because there might be no matching row in the 'windows_installations' table. $select->join('windows_installations', 'windows_installations.client_id = clients.id', $fromWindows, Select::JOIN_LEFT); } // apply filters if (!is_array($filter)) { // convert to array if necessary $filter = array($filter); $search = array($search); $operators = array($operators); $invert = array($invert); } foreach ($filter as $index => $type) { $arg = $search[$index]; $operator = $operators[$index]; $matchExact = $operator == 'eq'; $invertResult = $invert[$index]; switch ($type) { case '': break; // No filter requested // No filter requested case 'Id': if ($invertResult) { throw new \LogicException("invertResult cannot be used on Id filter"); } $select->where(array('clients.id' => $arg)); break; case 'AssetTag': case 'BiosDate': case 'BiosVersion': case 'CpuType': case 'DnsServer': case 'DefaultGateway': case 'Manufacturer': case 'Name': case 'OsName': case 'OsVersionNumber': case 'OsVersionString': case 'OsComment': case 'ProductName': case 'Serial': case 'UserAgent': case 'UserName': $select = $this->_filterByString($select, 'Client', $type, $arg, $matchExact, $invertResult, $addSearchColumns); break; case 'CpuClock': case 'CpuCores': case 'PhysicalMemory': case 'SwapMemory': $select = $this->_filterByOrdinal($select, 'Client', $type, $arg, $operator, $invertResult, $addSearchColumns); break; case 'InventoryDate': case 'LastContactDate': $select = $this->_filterByDate($select, 'Client', $type, $arg, $operator, $invertResult, $addSearchColumns); break; case 'PackagePending': case 'PackageRunning': case 'PackageSuccess': case 'PackageError': if ($invertResult) { throw new \LogicException("invertResult cannot be used on {$type} filter"); } $select = $this->_filterByPackage($select, $type, $arg, $addSearchColumns); break; case 'Software': if ($invertResult) { throw new \LogicException("invertResult cannot be used on Software filter"); } $select->quantifier('DISTINCT')->join('softwares', 'softwares.hardware_id = clients.id', $addSearchColumns ? array('software_version' => 'version') : array())->where(array('softwares.name' => $arg)); break; case 'MemberOf': if ($invertResult) { throw new \LogicException("invertResult cannot be used on MemberOf filter"); } // $arg is expected to be a \Model\Group\Group object. $arg->update(); $select->join('groups_cache', 'groups_cache.hardware_id = clients.id', $addSearchColumns ? array('static') : array())->where(array('groups_cache.group_id' => $arg['Id'], new Predicate\In('groups_cache.static', array(Client::MEMBERSHIP_AUTOMATIC, Client::MEMBERSHIP_ALWAYS)))); break; case 'ExcludedFrom': if ($invertResult) { throw new \LogicException("invertResult cannot be used on ExcludedFrom filter"); } // $arg is expected to be a \Model\Group\Group object. $arg->update(); $select->join('groups_cache', 'groups_cache.hardware_id = clients.id', array())->where(array('groups_cache.group_id' => $arg['Id'], 'groups_cache.static' => \Model\Client\Client::MEMBERSHIP_NEVER)); break; case 'Filesystem.Size': case 'Filesystem.FreeSpace': // Generic integer filter list($model, $property) = explode('.', $type); $select = $this->_filterByOrdinal($select, $model, $property, $arg, $operator, $invertResult, $addSearchColumns); break; default: if (preg_match('#^CustomFields\\.(.*)#', $type, $matches)) { $property = $matches[1]; $fieldType = $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getFields()[$property]; switch ($fieldType) { case 'text': case 'clob': $select = $this->_filterByString($select, 'CustomFields', $property, $arg, $matchExact, $invertResult, $addSearchColumns); break; case 'integer': case 'float': $select = $this->_filterByOrdinal($select, 'CustomFields', $property, $arg, $operator, $invertResult, $addSearchColumns); break; case 'date': $select = $this->_filterByDate($select, 'CustomFields', $property, $arg, $operator, $invertResult, $addSearchColumns); break; default: throw new \LogicException('Unsupported type: ' . $fieldType); } } elseif (preg_match('/^Registry\\.(.+)/', $type, $matches)) { $property = $matches[1]; $select = $this->_filterByString($select, 'Registry', $property, $arg, $matchExact, $invertResult, $addSearchColumns); } elseif (preg_match('/^([a-zA-Z]+)\\.([a-zA-Z]+)$/', $type, $matches)) { // apply a generic string filter. $select = $this->_filterByString($select, $matches[1], $matches[2], $arg, $matchExact, $invertResult, $addSearchColumns); } else { // Filter must be of the form 'Model.Property'. throw new \InvalidArgumentException('Invalid filter: ' . $type); } } } if ($order) { if (isset($map[$order])) { $order = "clients.{$map[$order]}"; } elseif ($order == 'Membership') { $order = 'groups_cache.static'; } elseif (preg_match('/^CustomFields\\.(.+)/', $order, $matches)) { $order = 'customfields_' . $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getColumnMap()[$matches[1]]; } elseif (preg_match('/^Windows\\.(.+)/', $order, $matches)) { $hydrator = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator(); $order = 'windows_' . $hydrator->extractName($matches[1]); } elseif (preg_match('/^Registry\\./', $order)) { $order = 'registry_content'; } elseif (preg_match('/^([a-zA-Z]+)\\.([a-zA-Z]+)$/', $order, $matches)) { $model = $matches[1]; $property = $matches[2]; // Assume column alias 'model_column' $tableGateway = $this->_serviceLocator->get('Model\\Client\\ItemManager')->getTable($model); $column = $tableGateway->getHydrator()->extractName($property); $order = strtolower("{$model}_{$column}"); } else { throw new \InvalidArgumentException('Invalid order: ' . $order); } $select->order(array($order => $direction)); } /* * Try to optimize the query by removing unnecessary JOINs. The query * can be rewritten if all of the following conditions are met: * - There is exactly 1 joined table. * - The tables are joined by an inner join. * - The only column from the 'clients' table is 'id'. * In that case, clients.id can be replaced by the 'hardware_id' or * 'client_id' column from the joined table. */ $joinedTables = $select->getRawState(Select::JOINS)->getJoins(); $clientColumns = $select->getRawState(Select::COLUMNS); if (count($joinedTables) == 1 and $clientColumns == array('id')) { $joinedTable = $joinedTables[0]; if ($joinedTable['type'] == Select::JOIN_INNER) { $joinColumn = strpos($joinedTable['on'], 'client_id') === false ? 'hardware_id' : 'client_id'; // Add column with "id" alias $columns = $joinedTable['columns']; $columns['id'] = $joinColumn; // Replace columns and table $select->from($joinedTable['name']); $select->columns($columns); $select->reset(Select::JOINS); // Replace possible clients.id in ORDER BY clause $orderSpec = array(); foreach ($select->getRawState(Select::ORDER) as $column => $direction) { if ($column == 'clients.id') { $column = $joinColumn; } $orderSpec[$column] = $direction; } $select->reset(Select::ORDER); $select->order($orderSpec); } } if ($query) { $resultSet = clone $clients->getResultSetPrototype(); $resultSet->initialize($sql->prepareStatementForSqlObject($select)->execute()); return $resultSet; } else { return $select; } }
public function ajaxArrayAction() { $table = new TableExample\ArrayAdapter(); $sql = new \Zend\Db\Sql\Sql($this->getDbAdapter()); $stmt = $sql->prepareStatementForSqlObject($this->getSource()); $res = $stmt->execute(); $resultSet = new \Zend\Db\ResultSet\ResultSet(); $resultSet->initialize($res); $source = $resultSet->toArray(); $table->setAdapter($this->getDbAdapter())->setSource($source)->setParamAdapter($this->getRequest()->getPost()); return $this->htmlResponse($table->render()); }
/** * Product list * * @return \ZfcDatagrid\Datagrid */ public function getDatagrid() { $eavProduct = new \Product\Model\EavProduct($this->tableGateway); $records = array(); $result = array(); $customAttributes = array(); $grid = $this->getGrid(); $grid->setId('productGrid'); $dbAdapter = $this->adapter; $select = new Select(); $select->from(array('p' => 'product')); // execute the query $sql = new \Zend\Db\Sql\Sql($this->adapter); $stmt = $sql->prepareStatementForSqlObject($select); $results = $stmt->execute(); // execute the main query $records = $this->tableGateway->select($select); // load the attributes from the preferences $columnsAttributesIdx = $this->settings->getValueByParameter('product', 'attributes'); if (!empty($columnsAttributesIdx)) { // get from the database the custom attributes set as product preferences ($columnsAttributesIdx) $selectedAttributes = $this->attributes->findbyIdx(json_decode($columnsAttributesIdx, true)); $selectedAttributes->buffer(); // Get the selected product attribute values ONLY $attributes = $eavProduct->loadAttributes($results, $selectedAttributes); $attributesValues = $attributes->toArray(); // loop the selected product attribute records foreach ($attributesValues as $recordId => $attributeValue) { // loop the record selected values foreach ($attributeValue as $id => $value) { // get the attribute information $theAttribute = $eavProduct->getAttribute($id); // create a temporary array of data to merge with the main datagrid array $customAttributes[$recordId][$theAttribute->getName()] = $value; // Create a custom column on the grid $col = new Column\Select($theAttribute->getName()); $col->setLabel(_($theAttribute->getLabel())); $grid->addColumn($col); } } // Merge the temporary array with the main datagrid array foreach ($records as $record) { $result[] = array_merge($record->getArrayCopy(), $customAttributes[$record->getId()]); } } $RecordsPerPage = $this->settings->getValueByParameter('product', 'recordsperpage'); $grid->setDefaultItemsPerPage($RecordsPerPage); $grid->setDataSource($result); $colId = new Column\Select('id'); $colId->setLabel('Id'); $colId->setIdentity(); $grid->addColumn($colId); $colType = new Type\DateTime('Y-m-d H:i:s', \IntlDateFormatter::SHORT, \IntlDateFormatter::SHORT); $colType->setSourceTimezone('Europe/Rome'); $colType->setOutputTimezone('UTC'); $colType->setLocale('it_IT'); $col = new Column\Select('createdat'); $col->setType($colType); $col->setLabel(_('Created At')); $col->setWidth(15); $grid->addColumn($col); // Add actions to the grid $showaction = new Column\Action\Button(); $showaction->setAttribute('href', "/admin/product/edit/" . $showaction->getColumnValuePlaceholder(new Column\Select('id'))); $showaction->setAttribute('class', 'btn btn-xs btn-success'); $showaction->setLabel(_('edit')); $delaction = new Column\Action\Button(); $delaction->setAttribute('href', '/admin/product/delete/' . $delaction->getRowIdPlaceholder()); $delaction->setAttribute('onclick', "return confirm('Are you sure?')"); $delaction->setAttribute('class', 'btn btn-xs btn-danger'); $delaction->setLabel(_('delete')); $col = new Column\Action(); $col->addAction($showaction); $col->addAction($delaction); $grid->addColumn($col); $grid->setToolbarTemplate(''); return $grid; }