/**
* Search scope
*
* @param Builder $query
* @param string $keywords
* @return Builder
*/
public function scopeSearch($query, $keywords)
{
//Return search results
return $query->where(function ($query) use($keywords) {
$query->where('first_name', 'like', $keywords . '%')->orWhere('last_name', 'like', $keywords . '%')->orWhereRaw('CONCAT(first_name, \' \',last_name) like \'' . $keywords . '%\'');
});
}
/**
* @param Builder $query
*
* @return mixed
*/
public function scopePublished($query)
{
return $query->where(function ($query) {
$query->where(function ($query) {
$query->where('published_at', '<=', Carbon::now())->orWhere('published_at', null);
});
$query->where(function ($query) {
$query->where('concealed_at', '>=', Carbon::now())->orWhere('concealed_at', null);
});
});
}
/**
* Resolves all the ScopeInterface items into the current Builder.
* @return \Illuminate\Database\Query\Builder $query
*/
public function getBuilder()
{
$this->query = clone $this->baseQuery;
//apply all direct scopes to the query.
$this->directScopes->each(function ($scope) {
$scope_method = 'apply';
$this->query = $scope->{$scope_method}($this->query);
});
//chain all required scopes in "AND" blocks
$this->requiredScopes->each(function ($scope) {
$scope_method = 'applyAnd';
$this->query = $scope->{$scope_method}($this->query);
});
//chain all optional scopes using "OR", nested within a single "AND" block.
if ($this->optionalScopes->count()) {
$this->query->where(function ($query) {
$this->optionalScopes->each(function ($scope) use($query) {
$scope_method = 'applyOr';
return $scope->{$scope_method}($query);
});
});
}
collect([])->merge($this->directScopes)->merge($this->requiredScopes)->merge($this->optionalScopes)->each(function ($scope) {
$this->parseScope($scope);
});
return $this->query;
}
/**
* Perform column search
*
* @return void
*/
public function doColumnSearch()
{
$input = $this->input;
$columns = $input['columns'];
// if older version, set the column name to query's fields
// or if new version but does not use mData support
if (!$this->new_version or !$this->mDataSupport and $this->new_version) {
for ($i = 0; $i < count($columns); $i++) {
if (!isset($this->columns[$i])) {
continue;
}
$columns[$i]['name'] = $this->columns[$i];
if (stripos($columns[$i]['name'], ' AS ') !== false or $columns[$i]['name'] instanceof Expression) {
$columns[$i]['name'] = '';
$columns[$i]['searchable'] = false;
$columns[$i]['orderable'] = false;
}
}
}
for ($i = 0, $c = count($columns); $i < $c; $i++) {
if ($columns[$i]['searchable'] == "true" and !empty($columns[$i]['search']['value']) and !empty($columns[$i]['name'])) {
$column = $columns[$i]['name'];
$keyword = $this->setupKeyword($columns[$i]['search']['value']);
// wrap column possibly allow reserved words to be used as column
$column = $this->wrapColumn($column);
if ($this->isCaseInsensitive()) {
$this->query->whereRaw('LOWER(' . $column . ') LIKE ?', [strtolower($keyword)]);
} else {
$col = strstr($column, '(') ? $this->connection->raw($column) : $column;
$this->query->where($col, 'LIKE', $keyword);
}
}
}
}
/**
* Set query options and params
* @param array $options
* @return Builder
*/
public function options($options = array())
{
// Get order options
$orderBy = array_get($options, 'order_by', 'created_at');
$order = array_get($options, 'order', 'desc');
// Run order
if ($orderBy == 'rand') {
$this->query->orderBy(DB::raw('RAND()'), $order);
} else {
$this->query->orderBy($orderBy, $order);
}
// Also the limit
if ($limit = array_get($options, 'limit')) {
$this->defaultLimit = (int) $limit;
}
if (is_array($options)) {
foreach ($options as $key => $value) {
if (!in_array($key, array('limit', 'order_by'))) {
if (is_array($value)) {
$this->query->where($key, $value[0], $value[1]);
} else {
$this->query->where($key, $value);
}
}
}
}
return $this->query;
}
/**
* Query scope that returns only the friendships of two users
*
* @param Builder $query The query builder object
* @param int $friendOneId The ID of the first user
* @param int $friendTwoId The ID of the second user
* @return Builder
*/
public function scopeAreFriends($query, $friendOneId, $friendTwoId)
{
return $query->where(function ($query) use($friendOneId, $friendTwoId) {
$query->whereSenderId($friendOneId)->whereReceiverId($friendTwoId);
})->orWhere(function ($query) use($friendOneId, $friendTwoId) {
$query->whereReceiverId($friendOneId)->whereSenderId($friendTwoId);
});
}
/**
* Query scope that returns the friendships of a user
*
* @param Builder $query The query builder object
* @param int $userId The ID of the user
* @param boolean $confirmed Only show confirmed friendships? Default = true
* @return Builder
*/
public function scopeFriendsOf($query, $userId, $confirmed = true)
{
if ($confirmed) {
$query->whereConfirmed(1);
}
return $query->where(function ($query) use($userId) {
$query->whereSenderId($userId)->orWhere('receiver_id', $userId);
});
}
/**
* Perform filter for global
*
* @param array $columns
* @param string $search
* @return self
*/
public function dtPerformGlobalFilter($columns, $search = '')
{
$this->dtModel = $this->dtModel->where(function ($query) use($columns, $search) {
foreach ($columns as $column) {
if (filter_var($column['searchable'], FILTER_VALIDATE_BOOLEAN) && !in_array($column['name'], $this->dtUnsearchable)) {
$query->orWhere(DB::raw($column['name']), 'LIKE', '%' . $search['value'] . '%');
}
}
});
return $this;
}
/**
* Method returns models geted by uuid
* @param Builder $query
* @param array|tring $uuid uuid or list of uuids
* @return Collection|Model Single model or collection of models
*/
public function scopeFindByUuid($query, $uuid)
{
if (!is_array($uuid)) {
if (!Uuid::isValid($uuid)) {
throw (new ModelNotFoundException())->setModel(get_class($this));
}
return $query->where('uuid', $uuid)->first();
} elseif (is_array($uuid)) {
array_map(function ($element) {
if (!Uuid::isValid($element)) {
throw (new ModelNotFoundException())->setModel(get_class($this));
}
}, $uuid);
return $query->whereIn('uuid', $uuid)->get();
}
}
/**
* Perform column search
*
* @param array $columns
* @return void
*/
public function doColumnSearch(array $columns)
{
for ($i = 0, $c = count($columns); $i < $c; $i++) {
if ($columns[$i]['searchable'] == "true" and !empty($columns[$i]['search']['value']) and !empty($columns[$i]['name'])) {
$column = $columns[$i]['name'];
$keyword = $this->setupKeyword($columns[$i]['search']['value']);
// wrap column possibly allow reserved words to be used as column
$column = $this->wrapColumn($column);
if ($this->isCaseInsensitive()) {
$this->query->whereRaw('LOWER(' . $column . ') LIKE ?', [strtolower($keyword)]);
} else {
$col = strstr($column, '(') ? $this->connection->raw($column) : $column;
$this->query->where($col, 'LIKE', $keyword);
}
}
}
}
/**
* Select only those forums the user has access to.
* WARNING: Creates JOINs with the forum_threads and the forums table.
*
* @param Builder $query The Eloquent Builder object
* @param User $user User model or null if it's the current client
* @return Builder
*/
public function scopeIsAccessible($query, $user = null)
{
$query->select('forum_posts.*')->join('forum_threads', 'forum_posts.thread_id', '=', 'forum_threads.id')->join('forums', 'forum_threads.forum_id', '=', 'forums.id');
if (!$user) {
$user = user();
}
if ($user) {
$internal = $user->hasAccess('internal');
$teamIds = DB::table('team_user')->whereUserId($user->id)->lists('team_id');
$teamIds[] = -1;
// Add -1 as team ID so the SQL statements (`team_id` in (...)) always has valid syntax
return $query->where('internal', '<=', $internal)->where(function ($query) use($teamIds) {
$query->whereNull('team_id')->orWhereIn('team_id', $teamIds);
});
} else {
return $query->whereInternal(0)->whereNull('team_id');
}
}
/**
* Applies a translatable index to a basic query. This scope will join the index
* table and cannot be executed more than once.
* @param Builder $query
* @param string $index
* @param string $value
* @param string $locale
* @return Builder
*/
public function scopeTransWhere($query, $index, $value, $locale = null)
{
if (!$locale) {
$locale = $this->translatableContext;
}
$query->select($this->model->getTable() . '.*');
$query->where(function ($q) use($index, $value) {
$q->where($this->model->getTable() . '.' . $index, $value);
$q->orWhere(function ($q) use($index, $value) {
$q->where('rainlab_translate_indexes.item', $index)->where('rainlab_translate_indexes.value', $value);
});
});
// This join will crap out if this scope executes twice, it is a known issue.
// It should check if the join exists before applying it, this mechanism was
// not found in Laravel. So options are block joins entirely or allow once.
$query->leftJoin('rainlab_translate_indexes', function ($join) use($locale) {
$join->on(Db::raw(DbDongle::cast($this->model->getQualifiedKeyName(), 'TEXT')), '=', 'rainlab_translate_indexes.model_id')->where('rainlab_translate_indexes.model_type', '=', get_class($this->model))->where('rainlab_translate_indexes.locale', '=', $locale);
});
return $query;
}
/**
* A scope filter to only show items which have a given string in one
* or more of it's columns, defined by the searchables property
*
* @param Builder $query Original query
* @param string $search_term term to search for
* @param boolean $loose whether to form exact matches only
* @return Builder Filtered query
*/
public function scopeSimpleSearch($query, $search_term, $loose = true)
{
if ($loose) {
$search_term = "%{$search_term}%";
}
return $query->where(function ($sub_query) use($search_term) {
$searchables = $this->getSearchables();
if (count($searchables) === 0) {
throw new Exception("Attempting to search Model with no fields to search by");
} elseif (count($searchables) === 1) {
return $sub_query->where($searchables[0], 'LIKE', $search_term);
} else {
// Pop the first item to initiate a where set
$first = array_shift($searchables);
$sub_query->where($first, 'LIKE', $search_term);
// Then loop through the remainder to add them as 'or where' participants
foreach ($searchables as $column_name) {
$sub_query->orWhere($column_name, 'LIKE', $search_term);
}
}
return $sub_query;
});
}
public function scopePatchId(Builder $query, $patch_id)
{
return $query->where('patch_id', $patch_id);
}
/**
* Root scope
*
* @param Builder $query
* @return Builder $query
*/
public function scopeInRoot($query)
{
return $query->where($this->getContainerKeyName(), null);
}
/**
* Create default where for builder
* @return void
*/
protected function defaultBuilderWhere(Builder $builder)
{
$pk = $this->table->pk();
if ($pk && isset($this->data[$pk])) {
$builder->where($pk, '=', $this->data[$pk]);
} else {
foreach ($this->data as $key => $val) {
if (array_key_exists($key, $this->modifiedData)) {
continue;
}
$builder->where($key, '=', $val);
}
}
$builder->limit(1);
}
/**
* @param string $category
* @return Builder
*/
public function scopeWhereCategoryIs(Builder $q, $category)
{
//aliases
if ($category == 'contorni') {
$category = 'Verdure, ortaggi e contorni';
} elseif ($category == 'primi') {
$category = 'Primi Piatti';
} elseif ($category == 'secondi') {
$category = ['Secondi di carne', 'Secondi di pesce'];
}
//dd($category);
if (is_array($category)) {
return $q->whereIn('category', $category);
} else {
return $q->where('category', '=', $category);
}
}
/**
* Scope the query to a specific category.
*
* @param Builder $query Query object
* @param string $category
* @return void
*/
public function scopeGetCategory($query, $category)
{
$query->where('category', $category);
}
/**
* Scope queary to those located at a given address
*
* @param Builder $query
* @param string $uri
*
* @return mixed
*/
public function scopeFindEventByUri($query, $uri)
{
$sections = explode('/', $uri);
$event = $query->where([['zip', $sections[0]], ['name', str_replace('-', ' ', $sections[1])]])->first();
return $event;
}
/**
* @param string $type
* @return Builder
*/
public function scopeWhereTypeIs(Builder $q, $type)
{
return $q->where('type', '=', ${$type});
}
/**
* Has role scope.
*
* @param Builder $query
* @param string $type
* @return Builder
*/
public function scopeHasRole($query, $type)
{
return $query->whereHas('roles', function ($query) use($type) {
$query->where('slug', $type);
});
}
/**
* Service id scope
*
* @param Builder $query
* @param string $id
* @return Builder
*/
public function scopeService($query, $id)
{
return $query->where("service_message_id", $id);
}
/**
* Static query scope. Returns a query scope with
* the page that matches the url, the language code
* and the scoped attributes, if any.
*
* @param Builder $query
* @param string $url
* @param string $lang_code
* @param array $scoped
*
* @return string
*/
public function scopeMatch($query, $url, $lang_code, $scoped = [])
{
$slug = new Slug();
if (starts_with($url, '/')) {
$url = substr($url, 1, strlen($url));
}
$tokens = explode('/', $url);
return $query->where($scoped)->whereHas('slugs', function ($query) use($slug, $lang_code, $tokens) {
$query->where($slug->getLangColumnKey(), $lang_code)->whereIn($slug->getSlugColumnKey(), $tokens);
})->orderBy($this->getQualifiedDepthColumnName(), 'DESC')->firstOrFail();
}
/**
* Process STRING type of search
* @param Builder $query
* @param string $key
* @param array $values
*/
protected function setStringSearchQuery($query, $key, array $values)
{
$value = $values['values'][0];
switch ($values['operator']) {
case '~':
if (isset(static::$searchParams[$key]['relation'])) {
$relationKey = static::$searchParams[$key]['relation'][1];
$query->whereHas(static::$searchParams[$key]['relation'][0], function ($q) use($relationKey, $value) {
$q->where($relationKey, 'LIKE', "%{$value}%");
});
} else {
$query->where($key, 'LIKE', "%{$value}%");
}
break;
case '!~':
if (isset(static::$searchParams[$key]['relation'])) {
$relationKey = static::$searchParams[$key]['relation'][1];
$query->whereHas(static::$searchParams[$key]['relation'][0], function ($q) use($relationKey, $value) {
$q->where($relationKey, 'NOT LIKE', "%{$value}%");
});
} else {
$query->where($key, 'NOT LIKE', "%{$value}%");
}
break;
}
}
/**
* Applies a filter scope constraints to a DB query.
* @param string $scope
* @param Builder $query
* @return Builder
*/
public function applyScopeToQuery($scope, $query)
{
if (is_string($scope)) {
$scope = $this->getScope($scope);
}
if (!$scope->value) {
return;
}
switch ($scope->type) {
case 'string':
if ($scope->value && is_scalar($scope->value) && trim($scope->value)) {
if (array_get($scope->config, 'isNumericFindId') && is_numeric(trim($scope->value))) {
$query->where('id', trim($scope->value));
} else {
$query->searchWhere($scope->value, array_get($scope->config, 'fields') ?: [$scope->scopeName]);
}
}
break;
case 'date':
case 'range':
$this->applyDateScopeToQuery($scope, $query);
break;
default:
$value = is_array($scope->value) ? array_keys($scope->value) : $scope->value;
/*
* Condition
*/
if ($scopeConditions = $scope->conditions) {
foreach ((array) $scopeConditions as $scopeKey => $scopeCondition) {
if (is_array($scopeConditions)) {
if (array_key_exists($scopeKey, $scope->value) == false) {
continue;
}
}
if (is_array($value)) {
$filtered = implode(',', array_build($value, function ($key, $_value) {
return [$key, Db::getPdo()->quote($_value)];
}));
} else {
$filtered = Db::getPdo()->quote($value);
}
$query->whereRaw(strtr($scopeCondition, [':filtered' => $filtered]));
}
}
}
/*
* Scope
*/
if ($scopeMethod = $scope->scope) {
$query->{$scopeMethod}($value);
}
return $query;
}
/**
* @param array $queries {
*
* @type string $relation Optional. The MySQL keyword used to join the clauses of the query. Accepts 'AND', Or
* 'OR'. Default 'AND'.
*
* @type array {
* @type string $key Meta key to filter by.
* @type string $value Meta value to filter by.
* @type string $compare MySQL operator used for comparing the $value. Accepts '=',
* '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN',
* 'BETWEEN', 'NOT BETWEEN', 'REGEXP', 'NOT REGEXP', or 'RLIKE'.
* Default is '='
* }
* }
*
* @param Builder $q
* @param int $depth
*/
public function metaQueryClause($queries, &$q, $depth = 0)
{
$i = 0;
foreach ($queries as $key => $query) {
if ('relation' === $key) {
//if relation were found we shall skip to the next iteration
continue;
} else {
if (isset($query['key'])) {
// That happens when a non-nested query found on the first iteration
// in that case we the clause should be AND
$relation = 0 === $i ? 'AND' : $queries['relation'];
$q->where(function ($q) use($query) {
if (isset($query['key'])) {
$q->where('meta_key', '=', $query['key']);
}
if (isset($query['value'])) {
if (isset($query['compare'])) {
switch (strtolower($query['compare'])) {
case 'between':
$q->whereBetween('meta_value', $query['value']);
break;
case 'not between':
$q->whereNotBetween('meta_value', $query['value']);
break;
case 'in':
$q->whereIn('meta_value', $query['value']);
break;
case 'not in':
$q->whereNotIn('meta_value', $query['value']);
break;
case 'is null':
$q->whereIsNull('meta_value');
break;
case 'is not null':
$q->whereIsNotNull('meta_value');
break;
default:
$q->where('meta_value', $query['compare'], $query['value']);
}
} else {
$q->where('meta_value', '=', $query['value']);
}
}
}, null, null, $relation);
} else {
if (is_array($query)) {
$depth = $depth + 1;
// If we're going recursive for the first iterate we shall make the relation AND by default
// else we should use the provided relation
$relation = 1 === $depth && 0 === $i ? 'AND' : $queries['relation'];
$q->where(function ($q) use($query, $depth) {
$this->metaQueryClause($query, $q, $depth);
}, null, null, $relation);
} else {
continue;
}
}
}
$i++;
}
}
/**
* Scope query to those located at a given adress
* @param Builder $query
* @param string $zip
* @param string $street
* @return Builder
*/
public function scopeLocatedAt($query, $zip, $street)
{
$street = str_replace('-', ' ', $street);
return $query->where(compact('zip', 'street'));
}
/**
*@param Builder $query
*@param string $title
*@param string $flavor
*@return Builder
*/
public function scopelocatedAt($query, $title, $flavor)
{
$flavor = str_replace('-', ' ', $flavor);
return $query->where(compact('title', 'flavor'));
}
