/** * 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; }
/** * 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); }
/** * 查询今天的数据 * * @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())]); }
/** * 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); }
/** * 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); }
/** * DateBetween * * @param $key * @param $value * @return $this */ private function setDateBetween($key, $value) { $this->query->whereBetween(\DB::raw('DATE(' . $key . ')'), $value); return $this; }
/** * 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()]); }