/**
* 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()]);
}