Add a relationship count / exists condition to the query with where clauses.
public static function scopeWithTag(Builder $query, $tags, $type = 'slug')
{
$tags = (new static())->prepareTags($tags);
return $query->whereHas('tags', function ($query) use($type, $tags) {
$query->whereIn($type, $tags);
});
}
/**
* @param Builder|\Illuminate\Database\Eloquent\Builder|Model $query
* @return mixed
*/
public function scopeVisible($query)
{
return $query->whereHas('profile', function ($query) {
/** @var Profile $query */
return $query->visible();
});
}
/**
* 关联查询
* @param $relation
* @param \Closure $callback 由于此处 Closure 接受的参数是 Table 类，所以下面调用时封装了一次
* @return static
*/
public function whereHas($relation, $callback)
{
$this->original->whereHas($relation, function ($query) use($callback) {
call_user_func($callback, lego_table($query));
});
return $this;
}
/**
* アクティブな日誌があるか
* @param Builder $query
* @param $cropId
* @return Builder|static
*/
public function scopeHasActiveDiary(Builder $query, $cropId = null)
{
return $query->whereHas('workDiaries', function ($query) use($cropId) {
$query->where('archive', false);
if (!is_null($cropId)) {
$query->where('crop_id', $cropId);
}
});
}
/**
* query filter with id scope
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeFilter($query, $request)
{
// filter id
if ($request->has('id')) {
$query->where('id', $request->get('id'));
}
// filter title
if ($request->has('title')) {
$query->where('title', 'like', "%{$request->get('title')}%");
}
// filter category
if ($request->has('category')) {
$query->whereHas('category', function ($query) use($request) {
$query->where('name', 'like', "%{$request->get('category')}%");
});
}
// filter status
if ($request->has('status')) {
$query->where('is_publish', $request->get('status'));
}
// filter created_at
if ($request->has('created_at_from')) {
$query->where('created_at', '>=', Carbon::parse($request->get('created_at_from')));
}
if ($request->has('created_at_to')) {
$query->where('created_at', '<=', Carbon::parse($request->get('created_at_to')));
}
return $query;
}
/**
* Constrain the given users query by all provided roles.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $role
* @return \Illuminate\Database\Eloquent\Builder
*/
public function constrainWhereIsAll($query, $role)
{
$roles = array_slice(func_get_args(), 1);
return $query->whereHas('roles', function ($query) use($roles) {
$query->whereIn('name', $roles);
}, '=', count($roles));
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
*/
public function apply(Builder $builder, Model $model)
{
$builder->whereHas('meta', function ($query) {
$query->where('meta_key', '_apiposts_enable_api_key')->where('meta_value', 'yes');
self::$apiEnabledScopeColumn = $query->toSql();
});
}
/**
* Scope a query to only include guilds that are fully formed.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeFormed(Builder $query)
{
return $query->whereHas('memberships', function ($membership) {
$membership->whereHas('rank', function ($rank) {
$rank->where('level', '>', 1);
});
}, '>=', 4);
}
/**
* Apply scope on the query.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
*
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$column = $model->getApplicationForeignKey(true);
$builder->whereHas('applications', function ($q) use($column) {
$q->where($column, CurrentApplication::get()->id);
});
$this->addAllApplications($builder);
}
/**
* @param Builder $builder
* @param Model $model
*/
public function apply(Builder $builder, Model $model)
{
if (!Auth::guest() && Auth::user()->hasCountryRole()) {
$country = Auth::user()->country;
if ($builder->getModel()->getTable() == "activity_logs") {
$builder->whereHas('contract', function ($q) use($country) {
$q->whereRaw("contracts.metadata->'country'->>'code' in (?)", $country);
});
} elseif ($builder->getModel()->getTable() == "contract_annotations") {
$builder->whereHas('contract', function ($q) use($country) {
$q->whereRaw("contracts.metadata->'country'->>'code' in (?)", $country);
});
} else {
$builder->whereRaw("contracts.metadata->'country'->>'code' in (?)", $country);
}
}
}
public function apply(Builder $builder, Model $model)
{
$account = Account::getCurrent();
if (isset($account)) {
$builder->whereHas('account', function ($query) use($account) {
$query->where('accounts.id', $account->id);
});
}
}
/**
* Delete All notifications from a
* defined category
*
* @param $category_name int
* @param $expired Bool
* @return Bool
*/
public function deleteByCategory($category_name, $expired = false)
{
$query = $this->notification->whereHas('body', function ($q) use($category_name) {
$q->where('name', $category_name);
});
if ($expired == true) {
return $query->onlyExpired()->delete();
}
return $query->delete();
}
/**
* Applies constraint to query.
*
* @param Builder $builder query builder
* @param string $field field name
* @param string $mode determines how constraint is added to existing query ("or" or "and")
*/
public function apply(Builder $builder, $field, $mode = Constraint::MODE_AND)
{
if ($this->isRelation($field)) {
list($relation, $field) = $this->splitRelationField($field);
$builder->whereHas($relation, function (Builder $builder) use($field, $mode) {
$this->doApply($builder, $field, $mode);
});
} else {
$this->doApply($builder, $field, $mode);
}
}
/**
* @param Builder $query
* @param $column
* @param $value
*/
private function queryRelations(Builder $query, $column, $value)
{
list($relation_name, $column) = explode('.', $column, 2);
if (!is_array($value)) {
$query->whereHas($relation_name, function ($subQuery) use($column, $value) {
$subQuery->where($column, 'like', "%{$value}%");
});
} else {
//TODO: debatable
}
}
/**
* Restrict the resource to the given filters.
* These are the main functionalities:
*
* - Scope on the (single) relation!
* Use "-" as delimeter between the resource name and attribute.
* > /v1/items?article:drug_amount=1000mg
*
* - Use different operators!
* Use one of the operators as first character in the value.
* > /v1/items?article:cost=]5
* > /v1/items?article:cost=[20
* > /v1/items?article:name=~panadol
*
* - It now uses byCedric/Inquiry package!
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param array $values (default: array())
* @param string $relation_delimeter (default: -)
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeFilter($query, array $values = array())
{
// all allowed attributes
$attributes = array_keys($this->getArrayableItems(array_merge($this->attributes, array_flip($this->visible))));
// itterate over the provided queries
foreach ($values as $key => $value) {
// set some main variables
$inquiry = Inquiry::get($key);
$operator = null;
$relation = null;
$method = null;
// check if a valid operator was supplied
if (!$inquiry->hasOperator() && !$inquiry->hasRange()) {
continue;
}
// check if the key is a filterable attribute
if (!in_array($inquiry->getKey(), $attributes)) {
// if it doesn't has a relation, it's definitly not the good attribute
if (!$inquiry->swap()->hasRelation()) {
continue;
}
// get the relation, with the key (func:attr) as main value
$relation = $inquiry->swap()->getRelation();
$method = camel_case($relation->getRelated());
// check if the relation is allowed
if (!method_exists($this, $method) || !in_array($method, $attributes)) {
continue;
}
}
// check if the operator is a range query
if ($inquiry->hasRange()) {
// get the range values
$operator = $inquiry->getRange();
} else {
// get the operator query
$operator = $inquiry->getOperator();
}
// if a relation was provided
if ($relation !== null) {
// apply relation query
$query->whereHas($method, function ($query) use($operator, $relation) {
// apply final query
$this->applyQuery($query, $relation->getValue(), $operator);
});
// query already applied
continue;
}
// apply query
$this->applyQuery($query, $inquiry->getKey(), $operator);
}
// return the query for method chaining
return $query;
}
/**
* Filters by a relationship that isn't joined by using that relation's ModelFilter.
*
* @param $related
* @param $filterableInput
*/
public function filterUnjoinedRelation($related)
{
$this->query->whereHas($related, function ($q) use($related) {
$this->callRelatedLocalSetup($related, $q);
// If we defined it locally then we're running the closure on the related model here right.
foreach ($this->getLocalRelation($related) as $closure) {
// Run in context of the related model locally
$closure($q);
}
if (count($filterableRelated = $this->getRelatedFilterInput($related)) > 0) {
$q->filter($filterableRelated);
}
return $q;
});
}
/**
* Apply the filter to relationship query builder.
*
* @param string $key
* @param string $value
* @return void
*/
protected function filterRelation($key, $value)
{
$key = str_replace('~', '.', $key);
$parts = explode('.', $key);
$realKey = array_pop($parts);
$relation = implode('.', $parts);
if (!in_array($relation, array_keys($this->with))) {
return;
}
extract($this->formatParam($realKey, $value));
$this->builder->whereHas($relation, function ($q) use($column, $comparator, $value) {
if ($comparator == 'IN') {
$values = explode(',', $value);
$q->whereIn($column, $values);
} elseif ($comparator == 'NOT IN') {
$values = explode(',', $value);
$q->whereNotIn($column, $values);
} else {
$values = explode('|', $value);
if (count($values) > 1) {
$q->where(function ($query) use($column, $comparator, $values) {
foreach ($values as $value) {
if ($comparator == 'LIKE' || $comparator == 'NOT LIKE') {
$value = preg_replace('/(^\\*|\\*$)/', '%', $value);
}
// Link the filters with AND of there is a "not" and with OR if there's none
if ($comparator == '!=' || $comparator == 'NOT LIKE') {
$query->where($column, $comparator, $value);
} else {
$query->orWhere($column, $comparator, $value);
}
}
});
} else {
$value = $values[0];
if ($comparator == 'LIKE' || $comparator == 'NOT LIKE') {
$value = preg_replace('/(^\\*|\\*$)/', '%', $value);
}
if ($comparator == 'NULL' || $comparator == 'NOT NULL') {
$q->whereNull($column, 'and', $comparator == 'NOT NULL');
} else {
$q->where($column, $comparator, $value);
}
}
}
});
}
/**
* @param RepositoryInterface $repository
* @param NamedColumnInterface $column
* @param Builder $query
* @param string $search
* @param array|string $fullSearch
* @param string $operator
*
* @return void
*/
public function apply(RepositoryInterface $repository, NamedColumnInterface $column, Builder $query, $search, $fullSearch, $operator = '=')
{
if (empty($search)) {
return;
}
if ($operator == 'like') {
$search = '%' . $search . '%';
}
$name = $column->getName();
if ($repository->hasColumn($name)) {
$query->where($name, $operator, $search);
} elseif (strpos($name, '.') !== false) {
$parts = explode('.', $name);
$fieldName = array_pop($parts);
$relationName = implode('.', $parts);
$query->whereHas($relationName, function ($q) use($search, $fieldName, $operator) {
$q->where($fieldName, $operator, $search);
});
}
}
/**
* Applies constraint to query.
*
* @param Builder $builder query builder
* @param string $field field name
*/
public function apply(Builder $builder, $field)
{
if (strpos($field, ':') !== false) {
list($model, $column) = explode(':', $field);
$model = explode('_', $model);
for ($i = 1; $i < count($model); $i++) {
$model[$i] = ucfirst($model[$i]);
}
$model = implode($model);
$field = str_replace(':', '.', $field);
$builder->whereHas($model, function ($query) use($field) {
$this->apply($query, $field);
});
} elseif ($this->operator == Constraint::OPERATOR_IN) {
$builder->whereIn($field, $this->value);
} elseif ($this->operator == Constraint::OPERATOR_NOT_IN) {
$builder->whereNotIn($field, $this->value);
} else {
$builder->where($field, $this->operator, $this->value);
}
}
/**
* Scopes the specified query by a labels name.
*
* @param Builder $query
* @param string $resolution
*
* @return Builder
*/
public function scopeHasResolution(Builder $query, $resolution = '')
{
if (!empty($resolution)) {
if ($resolution === 'yes') {
$query->whereHas('comments', function (Builder $query) {
$query->where(['resolution' => true]);
});
}
}
return $query;
}
public function apply(Builder $builder, Model $model)
{
$builder->whereHas('types', function ($q) {
$q->where('name', '=', 'Buyer');
});
}
/**
* @param Builder $query
* @param $locale
* @return Builder|static
*/
public function scopeTranslatedIn(Builder $query, $locale)
{
return $query->whereHas('translations', function (Builder $q) use($locale) {
$q->where($this->getLocaleKey(), '=', $locale);
});
}
/**
* Constrain the given roles query by the provided ability.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $ability
* @param \Illuminate\Database\Eloquent\Model|string|null $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function constrainRoles($query, $ability, $model = null)
{
$constraint = $this->getAbilityConstraint($ability, $model);
return $query->whereHas('abilities', $constraint);
}
/**
* Scope to select users having a specific
* role. Role can be an id or slug.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param int|string $role
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeRole($query, $role)
{
return $query->whereHas('roles', function ($query) use($role) {
$query->where(is_numeric($role) ? 'id' : 'slug', $role);
});
}
/**
* This scope filters results by checking the translation fields.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $type
* @param string $key
* @param string $value
*
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function scopeWhereExtensionAttribute(Builder $query, $type, $key, $value)
{
// We do this for querying, searching and sorting with source attributes
$this->setNodeTypeName($type);
return $query->whereHas('nodeSourceExtensions', function (Builder $query) use($key, $value) {
$query->where(source_table_name($this->getNodeTypeName()) . '.' . $key, $value);
});
}
public function scopeWithAnyTags(Builder $query, $tags = [])
{
$tags = Util::buildTagArray($tags);
if (empty($tags)) {
return $query->has('tags');
}
$slug = array_map([S::class, 'slugify'], $tags);
return $query->whereHas('tags', function ($q) use($slug) {
$q->whereIn('slug', $slug);
});
}
/**
* This scope filters results by checking the translation fields.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $key
* @param string $value
* @param string $locale
*
* @return \Illuminate\Database\Eloquent\Builder|static
*/
public function scopeWhereTranslation($query, $key, $value, $locale = null)
{
return $query->whereHas('translations', function ($query) use($key, $value, $locale) {
$query->where($this->getTranslationsTable() . '.' . $key, $value);
if ($locale) {
$query->where($this->getTranslationsTable() . '.' . $this->getLocaleKey(), $locale);
}
});
}
/**
* @param Builder $query
* @param array $conditions
* @return mixed
*/
public function scopeSearch(Builder $query, array $conditions)
{
// 見た目
if (isset($conditions['sex_s']) and $conditions['sex_s'] !== '0') {
$query->where('sex', $conditions['sex_s']);
}
// 年代
if (isset($conditions['age_s']) and $conditions['age_s'] !== '0') {
$query->where('age', $conditions['age_s']);
}
// 都道府県
if (isset($conditions['prefectures_s']) and $conditions['prefectures_s'] !== '0') {
$query->where('prefectures', $conditions['prefectures_s']);
}
// 地域
if (isset($conditions['area_s']) and $conditions['area_s'] !== '0') {
$areas = config::get('const.area_prefectures');
$areaPrefectures = $areas[$conditions['area_s']];
$query->whereIn('prefectures', $areaPrefectures);
}
// test 投稿を無視
$query->where('name', '<>', 'test');
$hasInterests = false;
$interests = Interest::all();
$keys = array_keys($conditions);
foreach ($interests as $row) {
if (in_array($row->name_tag, $keys)) {
$hasInterests = true;
break;
}
}
if ($hasInterests) {
foreach ($interests as $row) {
if (in_array($row->name_tag, $keys)) {
$query->whereHas('interests', function ($query) use($row) {
$query->where('id', $row->id);
});
}
}
}
$query->where('res_id', 0);
return $query;
}
/**
* Add a relationship count condition to the query with where clauses.
*
* @param string $relation
* @param \Closure $callback
* @param string $operator
* @param int $count
* @return \Illuminate\Database\Eloquent\Builder|static
* @static
*/
public static function whereHas($relation, $callback, $operator = '>=', $count = 1)
{
return \Illuminate\Database\Eloquent\Builder::whereHas($relation, $callback, $operator, $count);
}
/**
* Filter Users by Role.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param $role
* @return mixed
*/
public function scopeWhereHasRole($query, $role)
{
return $query->whereHas('roles', function ($userQuery) use($role) {
$userQuery->where('name', $role);
});
}
