PHP Sql::getSqlStringForSqlObject方法代码示例

 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 listItem($arrParam = null, $options = null)
 {
     if ($options['task'] == 'list-item-type-real-estate-child') {
         $sqlObj = new Sql($this->adapter);
         $select = $sqlObj->select()->from('category')->columns(array('id', 'name', 'parent'));
         $result = \ZendVN\Db\Sql\Result::toArray($select, $this->adapter);
         return $result;
     }
     if ($options['task'] == 'list-item-district') {
         $sqlObj = new Sql($this->adapter);
         $select = $sqlObj->select()->from('city_district')->columns(array('id', 'name', 'city_id'));
         $result = \ZendVN\Db\Sql\Result::toArray($select, $this->adapter);
         return $result;
     }
     if ($options['task'] == 'list-items-paginator') {
         $paginator = $arrParam['paginator'];
         $ssFilter = $arrParam['ssFilter'];
         $sqlObj = new Sql($this->adapter);
         $select = $sqlObj->select();
         $select->from(array('re' => 'register_email'))->join(array('ct' => 'city'), 'ct.id = re.city', array('name_city' => 'name'), $select::JOIN_LEFT)->join(array('ctd' => 'city_district'), 'ctd.id = re.district', array('name_district' => 'name'), $select::JOIN_LEFT)->join(array('retype' => 'category'), 'retype.id = re.cat_id', array('name_type' => 'name'), $select::JOIN_LEFT)->join(array('ju' => 'juridical'), 'ju.id = re.juridical', array('name_juridical' => 'name'), $select::JOIN_LEFT)->where('re.user_id =' . $arrParam['userId']);
         $sqlString = $sqlObj->getSqlStringForSqlObject($select);
         $result = $this->adapter->query($sqlString)->execute();
         return $result;
     }
 }

 public function saveItem($arrParam = null, $options = null)
 {
     if ($options['task'] == 'add') {
         $sqlObj = new \Zend\Db\Sql\Sql($this->adapter);
         $insertObj = $sqlObj->insert('service_account_register');
         $insertObj->values($arrParam);
         $sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
         $this->adapter->query($sqlString)->execute();
     }
     if ($options['task'] == 'edit') {
         $this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
     }
     if ($options['task'] == 'multi-status') {
         if (!empty($arrParam)) {
             foreach ($arrParam['id'] as $key => $value) {
                 if ($arrParam['type'] == 'multi-active') {
                     $status = 1;
                 }
                 if ($arrParam['type'] == 'multi-in-active') {
                     $status = 0;
                 }
                 $data = array('id' => $value, 'status' => $status);
                 $this->tableGateway->update($data, array('id' => $value));
             }
         }
     }
 }

 public function listItem($arrParam = null, $options = null)
 {
     if ($options['task'] == 'list-item-hop-thu-di') {
         $paginator = $arrParam['paginator'];
         $ssFilter = $arrParam['ssFilter'];
         $sqlObj = new Sql($this->adapter);
         $select = $sqlObj->select();
         $select->from(array('m' => 'message_send'))->join(array('u' => 'users'), 'u.id = m.user_id_receive', array('fullname' => 'fullname'), $select::JOIN_LEFT)->limit($paginator['itemCountPerPage'])->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
         $select->where('m.user_id_send =' . $arrParam['idUser']);
         $sqlString = $sqlObj->getSqlStringForSqlObject($select);
         $result = $this->adapter->query($sqlString)->execute();
         return $result;
     }
     if ($options['task'] == 'list-item-hop-thu-den') {
         $paginator = $arrParam['paginator'];
         $ssFilter = $arrParam['ssFilter'];
         $sqlObj = new Sql($this->adapter);
         $select = $sqlObj->select();
         $select->from(array('m' => 'message_receive'))->join(array('u' => 'users'), 'u.id = m.user_id_send', array('fullname' => 'fullname'), $select::JOIN_LEFT)->limit($paginator['itemCountPerPage'])->offset(($paginator['currentPageNumber'] - 1) * $paginator['itemCountPerPage']);
         $select->where('m.user_id_receive =' . $arrParam['idUser']);
         $sqlString = $sqlObj->getSqlStringForSqlObject($select);
         $result = $this->adapter->query($sqlString)->execute();
         return $result;
     }
 }

 protected function create(\Zend\Db\Sql\Ddl\CreateTable $dll)
 {
     return $this->generateCode(function () use($dll) {
         $adapter = $this->getTableGateway()->adapter;
         $sql = new Sql($adapter);
         $adapter->query($sql->getSqlStringForSqlObject($dll), $adapter::QUERY_MODE_EXECUTE);
     });
 }

 public function delete()
 {
     $delete = new ZfSql\Delete($this->tableName);
     $adapter = $this->sql->getAdapter();
     if ($this->select->where) {
         $delete->where($this->select->where);
     }
     $sqlString = $this->sql->getSqlStringForSqlObject($delete);
     try {
         $result = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
     } catch (AdapterException\ExceptionInterface $e) {
         throw new Exception\RecordNotDestroyedException($e->getMessage(), 0, $e);
     }
     if (!$result->count()) {
         throw new Exception\RecordNotDestroyedException("No rows were affected");
     }
     return true;
 }

 /**
  * 
  * @param string $tableName
  * @param array $data
  * @return bool
  */
 public function insert($tableName, array $data)
 {
     $sql = new Sql($this->adapter);
     $insert = $sql->insert($tableName);
     $insert->values($data);
     $sqlString = $sql->getSqlStringForSqlObject($insert);
     $results = $this->adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
     return $results;
 }

 public function addRole($serviceManager, $userRoleTable, $userIdField, $roleIdField, $userId, $roleId)
 {
     $adapter = $serviceManager->get('Zend\\Db\\Adapter\\Adapter');
     $sql = new Sql($adapter);
     $insert = $sql->insert()->into($userRoleTable)->columns(array($userIdField, $roleIdField))->values(array($userId, $roleId));
     $sqlString = $sql->getSqlStringForSqlObject($insert);
     $execute = $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
     return $execute;
 }

 public function insert($data)
 {
     $adapter = $this->adapter;
     $sql = new Sql($this->adapter);
     $insert = $sql->insert('item_price');
     $insert->values($data);
     $selectString = $sql->getSqlStringForSqlObject($insert);
     return $results = $this->adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
 }

 public function getUnUsuario()
 {
     $sql = new Sql($this->dbAdapter);
     $select = $sql->select();
     $select->columns(array('nombre', 'apellido', 'direccion', 'telefono'))->from('usuarios');
     $selectString = $sql->getSqlStringForSqlObject($select);
     $execute = $this->dbAdapter->query($selectString, Adapter::QUERY_MODE_EXECUTE);
     $result = $execute->toArray();
     return $result[0];
 }

 public function create($id)
 {
     $sql = new Sql($this->getAdapter());
     $insert = $sql->insert($this->getTableName());
     $insert->values(['id' => (string) $id]);
     $query = $sql->getSqlStringForSqlObject($insert);
     /** @var Result $results */
     $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
     return $results->getGeneratedValue();
 }

 public function fetchAll()
 {
     $adapter = $this->tableGateway->getAdapter();
     $sql = new Sql($adapter, 'Account');
     $select = $sql->select();
     $select->order('last_accessed ASC');
     $selectString = $sql->getSqlStringForSqlObject($select);
     $resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     return $resultSet;
 }

 public function checkHash($hash)
 {
     $adapter = $this->sm->get('Zend\\Db\\Adapter\\Adapter');
     $sql = new Sql($adapter);
     //search hash
     $select = $sql->select('tokens_table')->where(array('hash' => $hash))->order('id DESC');
     $selectString = $sql->getSqlStringForSqlObject($select);
     $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
     return $results;
 }

 public function roles()
 {
     $this->dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
     $adapter = $this->dbAdapter;
     $sql = new Sql($adapter);
     $select = $sql->select()->from('ta_rol');
     $selectString = $sql->getSqlStringForSqlObject($select);
     $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     return $results;
 }

 public function create(array $data)
 {
     $sql = new Sql($this->adapter);
     $this->adapter->getDriver()->getConnection()->beginTransaction();
     $insert = $sql->insert('places');
     $insert->values(['name' => $data['name'], 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]);
     $query = $sql->getSqlStringForSqlObject($insert);
     /** @var Result $results */
     $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
     $placeId = $results->getGeneratedValue();
     foreach ($data['types'] as $typeId) {
         $insert = $sql->insert('places_place_types');
         $insert->values(['place_id' => $placeId, 'place_type_id' => $typeId]);
         $q = $sql->getSqlStringForSqlObject($insert);
         $this->adapter->query($q, Adapter::QUERY_MODE_EXECUTE);
     }
     $this->adapter->getDriver()->getConnection()->commit();
     return $placeId;
 }