PHP Where::literal方法代码示例

 /**
  * @param bool $paginated
  * @param string $filter
  * @param string $orderBy
  * @param string $order
  * @return \Zend\Db\ResultSet\ResultSet|Paginator
  */
 public function fetchAll($paginated = false, $filter = '', $orderBy = 'voucherNo', $order = 'ASC')
 {
     $view = 'vw_account_receivable';
     if ($paginated) {
         $select = new Select($view);
         $select->order($orderBy . ' ' . $order);
         $where = new Where();
         $where->literal("concat_ws(' ',description, voucherNo, Type, amount, voucherDate, currencyCode) LIKE ?", '%' . $filter . '%')->and->equalTo('depositBy', $this->staffId);
         $select->where($where);
         return $this->paginateWith($select);
     }
     $tableGateway = new TableGateway($view, $this->adapter);
     return $tableGateway->select(array('depositBy' => $this->staffId));
 }

 /**
  * @param $filter
  * @param $orderBy
  * @param $order
  * @param string $source
  * @return Paginator
  * @throws \Exception
  */
 public function paginate($filter, $orderBy, $order, $source = '')
 {
     try {
         $metadata = new Metadata($this->adapter);
         if (empty($source)) {
             $source = $this->table;
         }
         $columns = $metadata->getColumnNames($source);
         $select = new Select($source);
         $select->order($orderBy . ' ' . $order);
         if (!empty($filter)) {
             $query = "CONCAT_WS(' '," . implode(',', $columns) . ') LIKE ?';
             $where = new Where();
             $where->literal($query, '%' . $filter . '%');
             $select->where($where);
         }
         $paginatorAdapter = new DbSelect($select, $this->adapter);
         return new Paginator($paginatorAdapter);
     } catch (\Exception $ex) {
         throw $ex;
     }
 }

 /**
  * @param  DatagridFilter $filter
  * @throws \Exception
  */
 public function applyFilter(DatagridFilter $filter)
 {
     $select = $this->getSelect();
     $adapter = $this->getSql()->getAdapter();
     $qi = function ($name) use($adapter) {
         return $adapter->getPlatform()->quoteIdentifier($name);
     };
     $col = $filter->getColumn();
     if (!$col instanceof Column\Select) {
         throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
     }
     $colString = $col->getSelectPart1();
     if ($col->getSelectPart2() != '') {
         $colString .= '.' . $col->getSelectPart2();
     }
     if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
         $colString = sprintf($col->getFilterSelectExpression(), $colString);
     }
     $values = $filter->getValues();
     $wheres = [];
     foreach ($values as $value) {
         $where = new Where();
         switch ($filter->getOperator()) {
             case DatagridFilter::LIKE:
                 $wheres[] = $where->like($colString, '%' . $value . '%');
                 break;
             case DatagridFilter::LIKE_LEFT:
                 $wheres[] = $where->like($colString, '%' . $value);
                 break;
             case DatagridFilter::LIKE_RIGHT:
                 $wheres[] = $where->like($colString, $value . '%');
                 break;
             case DatagridFilter::NOT_LIKE:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']);
                 break;
             case DatagridFilter::NOT_LIKE_LEFT:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
                 break;
             case DatagridFilter::NOT_LIKE_RIGHT:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']);
                 break;
             case DatagridFilter::EQUAL:
                 $wheres[] = $where->equalTo($colString, $value);
                 break;
             case DatagridFilter::NOT_EQUAL:
                 $wheres[] = $where->notEqualTo($colString, $value);
                 break;
             case DatagridFilter::GREATER_EQUAL:
                 $wheres[] = $where->greaterThanOrEqualTo($colString, $value);
                 break;
             case DatagridFilter::GREATER:
                 $wheres[] = $where->greaterThan($colString, $value);
                 break;
             case DatagridFilter::LESS_EQUAL:
                 $wheres[] = $where->lessThanOrEqualTo($colString, $value);
                 break;
             case DatagridFilter::LESS:
                 $wheres[] = $where->lessThan($colString, $value);
                 break;
             case DatagridFilter::BETWEEN:
                 $wheres[] = $where->between($colString, $values[0], $values[1]);
                 break 2;
             default:
                 throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
                 break;
         }
     }
     if (!empty($wheres)) {
         $set = new PredicateSet($wheres, PredicateSet::OP_OR);
         $select->where->andPredicate($set);
     }
 }

 public function init($params, $table, $whereSql = '')
 {
     $result['draw'] = (int) $params['draw'];
     $columns = $this->_extractColumns($params);
     $sql = new Sql($this->adapterDb);
     $where = new Where();
     $select = $sql->select()->from($table)->columns(array('total' => new Expression('COUNT(*)')));
     if ($whereSql) {
         $where->literal($whereSql);
         $select->where($where);
     }
     $statement = $sql->prepareStatementForSqlObject($select);
     $resultsSql = $statement->execute();
     $result['recordsTotal'] = (int) $resultsSql->current()['total'];
     $select = $sql->select()->from($table)->columns($columns);
     $globalSearch = [];
     if ($search = $params['search']['value']) {
         foreach ($columns as $col) {
             $globalSearch[] = "`{$col}` LIKE '%{$search}%'";
         }
     }
     if (!empty($globalSearch)) {
         $globalSearchSql = implode(' OR ', $globalSearch);
         $where->literal("({$globalSearchSql})");
     }
     $columnSearch = [];
     foreach ($params['columns'] as $index => $column) {
         if ($column['search']['value']) {
             if ($params['search']['regex'] == 'false') {
                 $columnSearch[] = "`{$columns[$index]}` LIKE '%{$column['search']['value']}%'";
             } else {
                 $columnSearch[] = "`{$columns[$index]}` = '{$column['search']['value']}'";
             }
         }
     }
     if (!empty($columnSearch)) {
         $columnSearchSql = implode(' AND ', $columnSearch);
         $where->literal("({$columnSearchSql})");
     }
     if ($whereSql) {
         $where->literal($whereSql);
     }
     $select->where($where);
     if (isset($params['order'])) {
         $arrayOrder = [];
         foreach ($params['order'] as $order) {
             $arrayOrder[$columns[$order['column']]] = $order['dir'];
         }
         $select->order($arrayOrder);
     }
     //        die($select->getSqlString());
     $statement = $sql->prepareStatementForSqlObject($select);
     $resultsSql = $statement->execute();
     $resultSet = new ResultSet();
     $resultSet->initialize($resultsSql);
     $result['recordsFiltered'] = (int) $resultSet->count();
     $dbSelect = new DbSelect($select, $this->adapterDb);
     $paginator = new Paginator($dbSelect);
     $paginator->setItemCountPerPage($params['length']);
     $paginator->setCurrentPageNumber($params['start'] / $params['length'] + 1);
     $array = [];
     foreach ($paginator as $index => $data) {
         foreach ($data as $value) {
             $array[$index][] = $value;
         }
     }
     $result['data'] = $array;
     $this->data = $result;
     return $this;
 }

 /**
  * @param  DatagridFilter            $filter
  * @throws \InvalidArgumentException
  */
 public function applyFilter(DatagridFilter $filter)
 {
     $select = $this->getSelect();
     $adapter = $this->getSql()->getAdapter();
     $qi = function ($name) use($adapter) {
         return $adapter->getPlatform()->quoteIdentifier($name);
     };
     $column = $filter->getColumn();
     $colString = $column->getSelectPart1();
     if ($column->getSelectPart2() != '') {
         $colString .= '.' . $column->getSelectPart2();
     }
     if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
         $colString = sprintf($column->getFilterSelectExpression(), $colString);
     }
     $values = $filter->getValues();
     $filterSelectOptions = $column->getFilterSelectOptions();
     $wheres = array();
     if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) {
         $where = new Where();
         $wheres[] = $where->between($colString, $values[0], $values[1]);
         if (count($wheres) > 0) {
             $set = new PredicateSet($wheres, PredicateSet::OP_AND);
             $select->where->andPredicate($set);
         }
     } else {
         foreach ($values as $value) {
             $where = new Where();
             switch ($filter->getOperator()) {
                 case DatagridFilter::LIKE:
                     $wheres[] = $where->like($colString, '%' . $value . '%');
                     break;
                 case DatagridFilter::LIKE_LEFT:
                     $wheres[] = $where->like($colString, '%' . $value);
                     break;
                 case DatagridFilter::LIKE_RIGHT:
                     $wheres[] = $where->like($colString, $value . '%');
                     break;
                 case DatagridFilter::NOT_LIKE:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
                     break;
                 case DatagridFilter::NOT_LIKE_LEFT:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
                     break;
                 case DatagridFilter::NOT_LIKE_RIGHT:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
                     break;
                 case DatagridFilter::EQUAL:
                     $wheres[] = $where->equalTo($colString, $value);
                     break;
                 case DatagridFilter::NOT_EQUAL:
                     $wheres[] = $where->notEqualTo($colString, $value);
                     break;
                 case DatagridFilter::GREATER_EQUAL:
                     $wheres[] = $where->greaterThanOrEqualTo($colString, $value);
                     break;
                 case DatagridFilter::GREATER:
                     $wheres[] = $where->greaterThan($colString, $value);
                     break;
                 case DatagridFilter::LESS_EQUAL:
                     $wheres[] = $where->lessThanOrEqualTo($colString, $value);
                     break;
                 case DatagridFilter::LESS:
                     $wheres[] = $where->lessThan($colString, $value);
                     break;
                 case DatagridFilter::BETWEEN:
                     $wheres[] = $where->between($colString, $values[0], $values[1]);
                     break;
                 case DatagridFilter::IN:
                     $wheres[] = $where->in($colString, (array) $value);
                     break;
                 case DatagridFilter::NOT_IN:
                     $wheres[] = $where->notin($colString, (array) $value);
                     break;
                 default:
                     throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
                     break;
             }
         }
         if (count($wheres) > 0) {
             $set = new PredicateSet($wheres, PredicateSet::OP_OR);
             $select->where->andPredicate($set);
         }
     }
 }