PHP Builder::whereRaw方法代码示例

 /**
  * Parse the fulltext search parameter q
  *
  * @param  string $qParam
  * @param  array  $fullTextSearchColumns
  * @return void
  */
 protected function parseFullTextSearch($qParam, $fullTextSearchColumns)
 {
     if ($qParam == '') {
         //Add where that will never be true
         $this->query->whereRaw('0 = 1');
         return;
     }
     $fulltextType = Config::get('apihandler.fulltext');
     if ($fulltextType == 'native') {
         //Use pdo's quote method to be protected against sql-injections.
         //The usual placeholders unfortunately don't seem to work using AGAINST().
         $qParam = $this->query->getConnection()->getPdo()->quote($qParam);
         //Use native fulltext search
         $this->query->whereRaw('MATCH(' . implode(',', $fullTextSearchColumns) . ') AGAINST("' . $qParam . '" IN BOOLEAN MODE)');
         //Add the * to the selects because of the score column
         if (count($this->query->columns) == 0) {
             $this->query->addSelect('*');
         }
         //Add the score column
         $scoreColumn = Config::get('apihandler.fulltext_score_column');
         $this->query->addSelect($this->query->raw('MATCH(' . implode(',', $fullTextSearchColumns) . ') AGAINST("' . $qParam . '" IN BOOLEAN MODE) as `' . $scoreColumn . '`'));
     } else {
         $keywords = explode(' ', $qParam);
         //Use default php implementation
         $this->query->where(function ($query) use($fullTextSearchColumns, $keywords) {
             foreach ($fullTextSearchColumns as $column) {
                 foreach ($keywords as $keyword) {
                     $query->orWhere($column, 'LIKE', '%' . $keyword . '%');
                 }
             }
         });
     }
 }

 /**
  * like wheres function ,call by internal
  * @param array $conds
  * @return $this
  */
 protected function _wheres($conds)
 {
     foreach ($conds as $field => $opAndVal) {
         if (is_null($opAndVal)) {
             $opAndVal = [null];
         }
         $opAndVal = (array) $opAndVal;
         $op = strtolower(count($opAndVal) == 1 ? '=' : $opAndVal[0]);
         $val = last($opAndVal);
         $field = str_contains($field, '.') ? $field : $this->table . '.' . $field;
         switch ($op) {
             case 'in':
                 if (count($val) == 1) {
                     $this->operator->where($field, '=', $val[0]);
                 } else {
                     $this->operator->whereIn($field, $val);
                 }
                 break;
             case 'between':
                 $this->operator->whereBetween($field, $val);
                 break;
             case 'raw':
                 $this->operator->whereRaw($val);
                 break;
             default:
                 $this->operator->where($field, $op, $val);
         }
     }
     return $this;
 }

 /**
  * Apply where clauses on the subquery.
  *
  * @param  \Sofa\Searchable\Subquery $subquery
  * @param  \Sofa\Searchable\ColumnCollection $columns
  * @param  array $words
  * @return void
  */
 protected function searchWhere(array $columns, array $words, array $bindings)
 {
     $operator = $this->getLikeOperator();
     $wheres = [];
     foreach ($columns as $column) {
         $wheres[] = implode(' or ', array_fill(0, count($words), sprintf('%s %s ?', $column->getWrapped(), $operator)));
     }
     $where = implode(' or ', $wheres);
     $this->query->whereRaw("({$where})", $bindings);
 }

 /**
  * Lấy resources theo $level
  *
  * @param \Illuminate\Database\Query\Builder $query
  * @param int $level
  *
  * @return \Illuminate\Database\Query\Builder
  */
 public function scopeLevel($query, $level)
 {
     // Điều kiện: resources đang ở level $level
     $query->where("{$this->table}.level", '=', $level);
     if (!user()->inAdminGroup()) {
         switch ($level) {
             case ResourceLevel::LEVEL_CANHAN:
                 // Điều kiện: resources do chính user() tạo
                 $query->where("{$this->table}.user_id", '=', user('id'));
                 break;
             case ResourceLevel::LEVEL_DONVI:
                 // Điều kiện: resources của các user khác cùng đơn vị do user() làm thủ trưởng
                 if (user()->isGroupManager() && ($ids = user()->group->users->lists('id', 'username')->forget(user('username'))->all())) {
                     $query->whereIn("{$this->table}.user_id", $ids);
                 } else {
                     $query->whereRaw('1=0');
                 }
                 break;
             case ResourceLevel::LEVEL_COQUAN:
                 // Điều kiện: resources thuộc các categories user()-group được phép quản lý
                 if (user()->isGroupManager() && ($ids = user()->group->categories->lists('id')->all())) {
                     $query->whereIn("{$this->table}.category_id", $ids);
                 } else {
                     $query->whereRaw('1=0');
                 }
                 break;
             case ResourceLevel::LEVEL_BGH:
                 // Điều kiện: là thủ trưởng bgh
                 if (!user()->inBgh()) {
                     $query->whereRaw('1=0');
                 }
                 break;
             default:
                 $query->whereRaw('1=0');
         }
     }
     return $query;
 }

 /**
  * Add a raw where clause to the query.
  *
  * @param string $sql
  * @param array $bindings
  * @param string $boolean
  * @return $this 
  * @static 
  */
 public static function whereRaw($sql, $bindings = array(), $boolean = 'and')
 {
     return \Illuminate\Database\Query\Builder::whereRaw($sql, $bindings, $boolean);
 }

 /**
  * @param \Illuminate\Database\Query\Builder $query
  * @param string                             $mountNameOrId
  *
  * @return Builder
  */
 public function scopeByNameOrId($query, $mountNameOrId)
 {
     return $query->whereRaw('mount_id_text = :mount_id_text OR id = :id', [':mount_id_text' => $mountNameOrId, ':id' => $mountNameOrId]);
 }

 /**
  * @param Builder    $query
  * @param string|int $snapshotId
  *
  * @return Builder
  */
 public function scopeBySnapshotId($query, $snapshotId)
 {
     return $query->whereRaw('id = :id OR snapshot_id_text = :snapshot_id_text', ['id' => $snapshotId, 'snapshot_id_text' => $snapshotId]);
 }

 /**
  * @param Builder $query
  * @param integer $clusterId
  * @param integer $instanceId
  *
  * @return Builder
  */
 public function scopeByClusterInstance($query, $clusterId, $instanceId)
 {
     return $query->whereRaw('(cluster_id = :cluster_id OR cluster_id IS NULL) AND (instance_id = :instance_id OR instance_id IS NULL) AND active_ind = 1', [':cluster_id' => $clusterId, ':instance_id' => $instanceId]);
 }

 /**
  * @param \Illuminate\Database\Query\Builder $query
  * @param string|int                         $nameOrId
  *
  * @return Builder
  */
 public function scopeByNameOrId($query, $nameOrId)
 {
     return $query->whereRaw('server_id_text = :server_id_text OR id = :id', [':server_id_text' => $nameOrId, ':id' => $nameOrId]);
 }

 /**
  * @param Builder    $query
  * @param int|string $instanceNameOrId
  *
  * @return Builder
  */
 public function scopeByNameOrId($query, $instanceNameOrId)
 {
     return $query->whereRaw('instance_name_text = :instance_name_text OR instance_id_text = :instance_id_text or id = :id', [':instance_name_text' => $instanceNameOrId, ':instance_id_text' => $instanceNameOrId, ':id' => $instanceNameOrId]);
 }

 /**
  * @param Builder    $query
  * @param string|int $clusterNameOrId
  *
  * @return Builder
  */
 public function scopeByNameOrId($query, $clusterNameOrId)
 {
     return $query->whereRaw('cluster_id_text = :cluster_id_text OR id = :id', [':cluster_id_text' => $clusterNameOrId, ':id' => $clusterNameOrId]);
 }