Exemple #1
1
 /**
  * between, 两端开区间
  * @param $attribute
  * @param null $min
  * @param null $max
  * @return static
  */
 public function whereBetween($attribute, $min, $max)
 {
     if ($min > $max) {
         return $this->whereEquals(0, 1);
     }
     $this->original->whereBetween($attribute, [$min, $max]);
     return $this;
 }
 private function applyFilters(Builder $query, $expression, $value, $multiple = false)
 {
     $equalsFilter = false;
     $field = null;
     $filter = explode(":", $expression);
     // filter[field:filter]=value
     if (count($filter) == 2) {
         $field = $filter[0];
         $filter = $filter[1];
     } else {
         $equalsFilter = true;
         $field = $expression;
     }
     // filter[field]=value
     if ($equalsFilter) {
         $multiple ? $query->orWhere($field, '=', $value) : $query->where($field, '=', $value);
     }
     // filter[field:between]=A,B
     if ($this->isBetweenFilter($filter)) {
         $betweenValues = $this->getBetweenValues($value);
         if (count($betweenValues) != 2) {
             throw new BadRequestHttpException("Please specify two numbers to filter between");
         }
         $multiple ? $query->orWhereBetween($field, $betweenValues) : $query->whereBetween($field, $betweenValues);
     }
     // filter[field:[after, before, greater_than, less_than]]=value
     if ($this->isComparisonFilter($filter)) {
         if ($this->isDateFilter($field)) {
             $value = Carbon::createFromTimestamp($value);
         }
         $comparisonFilter = $this->getComparisonFilter($filter);
         $multiple ? $query->orWhere($field, $comparisonFilter, $value) : $query->where($field, $comparisonFilter, $value);
     }
     // filter[field:[contains, ends_with, starts_with]]=value
     if ($this->isWildcardFilter($filter)) {
         $value = $this->getLikeWildcard($filter, $value);
         $multiple ? $query->orWere($field, 'like', $value) : $query->where($field, 'like', $value);
     }
     // filter[field:trashed]=true
     if ($this->isTrashedFilter($field)) {
         $query->onlyTrashed();
     }
 }
 /**
  * Filter dates in query.
  *
  * @param  string $startDate
  * @param  string $endDate
  * @return $this
  */
 protected function filterDates($startDate = null, $endDate = null)
 {
     if (is_null($startDate) && !is_null($endDate)) {
         $this->query->where($this->gridQuery->dateReferenceColumn(), '<=', $endDate);
     }
     if (!is_null($startDate) && !is_null($endDate)) {
         $this->query->whereBetween($this->gridQuery->dateReferenceColumn(), [$startDate, $endDate]);
     }
     return $this;
 }
 /**
  * @param Builder $where
  * @param array   $filters
  * @param string  $boolean
  */
 protected static function apply(Builder $where, array $filters, $boolean)
 {
     foreach ($filters as $filterName => $valuePair) {
         foreach ($valuePair as $key => $value) {
             if (is_array($value) && count($value) > 0) {
                 $value = array_values($value);
                 if (count($value[0]) > 1) {
                     switch ($filterName) {
                         case BaseFilter::RANGES:
                             $where->whereBetween($key, [$value[0][0], $value[0][1]], $boolean);
                             break;
                         case BaseFilter::NOT_RANGES:
                             $where->whereNotBetween($key, [$value[0][0], $value[0][1]], $boolean);
                             break;
                     }
                 } else {
                     switch ($filterName) {
                         case BaseFilter::GROUP:
                             $where->whereIn($key, $value, $boolean);
                             break;
                         case BaseFilter::NOT_GROUP:
                             $where->whereNotIn($key, $value, $boolean);
                             break;
                     }
                 }
             }
             $value = (array) $value;
             $value = array_shift($value);
             switch ($filterName) {
                 case BaseFilter::GREATER_THAN_OR_EQUAL:
                     $where->where($key, '>=', $value, $boolean);
                     break;
                 case BaseFilter::GREATER_THAN:
                     $where->where($key, '>', $value, $boolean);
                     break;
                 case BaseFilter::LESS_THAN_OR_EQUAL:
                     $where->where($key, '<=', $value, $boolean);
                     break;
                 case BaseFilter::LESS_THAN:
                     $where->where($key, '<', $value, $boolean);
                     break;
                 case BaseFilter::CONTAINS:
                     $where->where($key, 'LIKE', '%' . $value . '%', $boolean);
                     break;
                 case BaseFilter::NOT_CONTAINS:
                     $where->where($key, 'NOT LIKE', '%' . $value . '%', $boolean);
                     break;
                 case BaseFilter::STARTS_WITH:
                     $where->where($key, 'LIKE', $value . '%', $boolean);
                     break;
                 case BaseFilter::ENDS_WITH:
                     $where->where($key, 'LIKE', '%' . $value, $boolean);
                     break;
                 case BaseFilter::EQUALS:
                     $where->where($key, '=', $value, $boolean);
                     break;
                 case BaseFilter::NOT_EQUAL:
                     $where->where($key, '!=', $value, $boolean);
                     break;
             }
         }
     }
 }
 public function applyFilter(Filter $filter, $operator, array $params)
 {
     $column = $filter->getField();
     $sqlLikeEscapes = ['%' => '\\%', '_' => '\\_'];
     switch ($operator) {
         // Zero-parameter operators
         case 'empty':
             $this->query->where(function ($query) use($column) {
                 $query->whereIn($column, ['', 0])->orWhereNull($column);
             });
             break;
         case 'not-empty':
             $this->query->where(function ($query) use($column) {
                 $query->whereNotIn($column, ['', 0])->whereNotNull($column);
             });
             break;
             // Single-parameter operators
         // Single-parameter operators
         case 'eq':
             $this->query->where($column, $params[0]);
             break;
         case 'ne':
             $this->query->where(function ($query) use($column, $params) {
                 $query->where($column, '!=', $params[0])->orWhereNull($column);
             });
             break;
         case 'lt':
             $this->query->where($column, '<', $params[0]);
             break;
         case 'gt':
             $this->query->where($column, '>', $params[0]);
             break;
         case 'lte':
             $this->query->where($column, '<=', $params[0]);
             break;
         case 'gte':
             $this->query->where($column, '>=', $params[0]);
             break;
         case 'starts-with':
             $this->query->where($column, 'LIKE', strtr($params[0], $sqlLikeEscapes) . '%');
             break;
         case 'ends-with':
             $this->query->where($column, 'LIKE', '%' . strtr($params[0], $sqlLikeEscapes));
             break;
         case 'contains':
             $this->query->where($column, 'LIKE', '%' . strtr($params[0], $sqlLikeEscapes) . '%');
             break;
         case 'not-starts-with':
             $this->query->where(function ($query) use($column, $params, $sqlLikeEscapes) {
                 $query->where($column, 'NOT LIKE', strtr($params[0], $sqlLikeEscapes) . '%')->orWhereNull($column);
             });
             break;
         case 'not-ends-with':
             $this->query->where(function ($query) use($column, $params, $sqlLikeEscapes) {
                 $query->where($column, 'NOT LIKE', '%' . strtr($params[0], $sqlLikeEscapes))->orWhereNull($column);
             });
             break;
         case 'not-contains':
             $this->query->where(function ($query) use($column, $params, $sqlLikeEscapes) {
                 $query->where($column, 'NOT LIKE', '%' . strtr($params[0], $sqlLikeEscapes) . '%')->orWhereNull($column);
             });
             break;
             // Dual-parameter operators
         // Dual-parameter operators
         case 'between':
             $this->query->whereBetween($column, $params);
             break;
         case 'not-between':
             $this->query->where(function ($query) use($column, $params, $sqlLikeEscapes) {
                 $query->whereNotBetween($column, $params)->orWhereNull($column);
             });
             break;
             // Unlimited-parameter operators
         // Unlimited-parameter operators
         case 'in':
             $this->query->whereIn($column, $params);
             break;
         case 'not-in':
             $this->query->whereNotIn($column, $params);
             break;
     }
     return $this;
 }
Exemple #6
0
 /**
  * Gets events between two dates.
  *
  * @param \Illuminate\Database\Eloquent\Builder $query
  * @param \DateTime $startsAt
  * @param \DateTime $endsAt
  * @return \Illuminate\Database\Eloquent\Builder
  */
 public function scopeBetweenDates($query, DateTime $startsAt, DateTime $endsAt)
 {
     return $query->whereBetween('starts_at', array($startsAt->copy()->setTime(0, 0, 0), $startsAt->copy()->setTime(23, 59, 59)))->where('ends_at', '<', $endsAt);
 }
 public function scopeFromDateTimeRange(Builder $query, $range, $exact = true)
 {
     if (!is_array($range)) {
         $range = DateTime::getDateTimeRange($range, $range, $exact);
     } else {
         $range = [(string) new Carbon($range[0]), (string) new Carbon($range[1])];
     }
     return $query->whereBetween('created_at', $range);
 }
Exemple #8
0
 /**
  * 查询今天的数据
  *
  * @param $query
  *
  * @return mixed
  */
 public function scopeToday(Builder $query)
 {
     return $query->whereBetween(static::CREATED_AT, [date('Y-m-d 00:00:00', time()), date('Y-m-d h:i:s', time())]);
 }
Exemple #9
0
 /**
  * Overwrite the whereBetween method to add the table name in front of the column
  *
  * @param $column
  * @param array $values
  * @param string $boolean
  * @param bool|false $not
  * @return mixed
  */
 public function whereBetween($column, array $values, $boolean = 'and', $not = false)
 {
     return parent::whereBetween($this->getModelTableColumn($column), $values, $boolean, $not);
 }
 /**
  * @param Builder $query
  * @param string  $field
  * @param string  $condition
  */
 protected function _query(Builder $query, $field, $condition = 'and')
 {
     $query->whereBetween($field, $this->getValue(), $condition);
 }
Exemple #11
0
 /**
  * Period query helper
  *
  * @param Builder               $query query builder
  * @param \DateTime|string      $since begin date
  * @param \DateTime|string|null $until end date
  * @return void
  */
 private function wherePeriod(Builder $query, $since, $until = null)
 {
     if ($until !== null) {
         $query->whereBetween($this->getTaggableTable() . '.createdAt', [$since, $until]);
     } else {
         $query->where($this->getTaggableTable() . '.createdAt', '>', $since);
     }
 }
 /**
  * Append where for a Quri operation
  *
  * @param Builder $builder
  * @param Operation $operation
  * @param $andOr
  * @return Builder
  * @throws ValidationException
  */
 public function applyOperation($builder, Operation $operation, $andOr)
 {
     $fieldName = $this->getRealFieldName($operation->fieldName());
     switch ($operation->operator()) {
         case "eq":
             $symbol = "=";
             break;
         case "neq":
             $symbol = "!=";
             break;
         case "gt":
             $symbol = ">";
             break;
         case "lt":
             $symbol = "<";
             break;
         case "gte":
             $symbol = ">=";
             break;
         case "lte":
             $symbol = "<=";
             break;
         case "like":
             $symbol = "like";
             break;
         case "between":
             return $builder->whereBetween($fieldName, $operation->values(), $andOr);
         case "in":
             return $builder->whereIn($fieldName, $operation->values(), $andOr);
         case "nin":
             return $builder->whereNotIn($fieldName, $operation->values(), $andOr);
         default:
             throw new ValidationException("QURI string could not be parsed. Operator '{$operation->operator()}' not supported");
     }
     return $builder->where($fieldName, $symbol, $operation->firstValue(), $andOr);
 }
Exemple #13
0
 /**
  * DateBetween
  *
  * @param $key
  * @param $value
  * @return $this
  */
 private function setDateBetween($key, $value)
 {
     $this->query->whereBetween(\DB::raw('DATE(' . $key . ')'), $value);
     return $this;
 }
Exemple #14
-1
 /**
  * Register periods macro.
  *
  * @param  \Illuminate\Database\Eloquent\Builder $query
  * @param  \Illuminate\Database\Eloquent\Model   $model
  * @return void
  */
 protected function registerMacro(Builder $query, Model $model)
 {
     /**
      * Query scope periods - filter this or last/next N periods
      *
      * @param  \Illuminate\Database\Eloquent\Builder $query
      * @param  string  $unit                                 Period type [minute|hour|day|week|month|year]
      * @param  integer $periods                              Number of past periods
      * @param  string  $column                               Column to match against
      * @param  integer $includeCurrent                       Whether to include current period in filter (additionally)
      * @return \Illuminate\Database\Eloquent\Builder
      *
      * @throws \InvalidArgumentException
      */
     $macro = function (Builder $query, $unit, $periods, $column = null, $includeCurrent = false) use($model) {
         if (!in_array($unit, ['minute', 'hour', 'day', 'week', 'month', 'year'])) {
             throw new InvalidArgumentException('Invalid period type');
         }
         // Developer may pass $includeCurrent instead of $column
         if (func_num_args() == 4 && is_bool($column)) {
             $includeCurrent = $column;
             $column = null;
         }
         $column = $column ?: $model->getPeriodColumnName();
         $range = $this->getPeriodRange($unit, $periods, $includeCurrent);
         return $query->whereBetween($column, $range);
     };
     $query->macro('periods', $macro);
 }
 /**
  * Scope a query to only include activities that starts in a certain month.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $query  the query to activities to be scoped
  * @param  int  $month  the number of months to be subtracted from the current month
  * @return \Illuminate\Database\Eloquent\Builder
  */
 public function scopeSubMonth($query, $month = 0)
 {
     $query->whereBetween('datetime_start', [Carbon::now()->subMonth($month)->startOfMonth(), Carbon::now()->subMonth($month)->endOfMonth()]);
 }