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(); } }
/** * Transfer local images to cloud storage. */ public function transferLocalImages() { $this->imageResource->orWhere(function (Builder $query) { $query->where('filename', '!=', ''); $query->whereNotNull('filename'); })->get()->each(function (Image $image) { $this->dispatcher->fire(new NewImageEvent($image)); }); }
/** * Search columns like fulltext do * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $word * @param array $fields * @return \Illuminate\Database\Eloquent\Builder */ public static function virtualFulltextSearchColumns(Builder $query, $word, $fields) { if (isset($word) && strlen($word) > 0) { $first = TRUE; foreach ($fields as $fieldName => $fieldAttributes) { /** * Empty attributes */ if (is_numeric($fieldName) == TRUE) { $fieldName = $fieldAttributes; $fieldAttributes = ['operator' => '=', 'prefix' => '', 'sufix' => '']; } if (isset($fieldAttributes['operator']) == FALSE) { $fieldAttributes['operator'] = '='; } if (isset($fieldAttributes['prefix']) == FALSE) { $fieldAttributes['prefix'] = ''; } if (isset($fieldAttributes['sufix']) == FALSE) { $fieldAttributes['sufix'] = ''; } /** * Query builder */ if ($first == TRUE) { $query->where($fieldName, $fieldAttributes['operator'], $fieldAttributes['prefix'] . $word . $fieldAttributes['sufix']); $first = FALSE; } else { $query->orWhere($fieldName, $fieldAttributes['operator'], $fieldAttributes['prefix'] . $word . $fieldAttributes['sufix']); } } } return $query; }
/** * Handle the filter. * * @param Builder $query * @param SearchFilterInterface $filter */ public function handle(Builder $query, TableBuilder $builder, SearchFilterInterface $filter) { $stream = $filter->getStream(); $model = $builder->getTableModel(); /** * If the model is translatable then * join it's translations so they * are filterable too. * * @var EloquentQueryBuilder $query */ if ($model->getTranslationModelName() && !$query->hasJoin($model->getTranslationTableName())) { $query->leftJoin($model->getTranslationTableName(), $model->getTableName() . '.id', '=', $model->getTranslationTableName() . '.' . $model->getRelationKey()); } $query->where(function (Builder $query) use($filter, $stream) { foreach ($filter->getColumns() as $column) { $query->orWhere($column, 'LIKE', "%{$filter->getValue()}%"); } foreach ($filter->getFields() as $field) { $filter->setField($field); $fieldType = $stream->getFieldType($field); $fieldTypeQuery = $fieldType->getQuery(); $fieldTypeQuery->setConstraint('or'); $this->container->call([$fieldTypeQuery, 'filter'], compact('query', 'filter', 'builder')); } }); }
/** * @param $value * @param array $in * @return Model */ public function searchFor($value, array $in) { foreach ($in as $key) { $this->model = $this->model->orWhere($key, $value); } return $this->model->firstOrFail(); }
private function notifyLast(Builder $query, $type) { $current = $this->ticket->events->first()->id; foreach ($this->ticket->actions as $action) { if ($action->id == $current) { break; } $last = $action; } return $query->orWhere('id', $last->user_id); }
/** * @param $search * @param $columns * @param \Illuminate\Database\Query\Builder|\Illuminate\Database\Eloquent\Builder $query * @return \Illuminate\Database\Query\Builder|\Illuminate\Database\Eloquent\Builder */ protected function search($search, $columns, $query) { if ($search) { $query->where(function ($query) use($search, $columns) { foreach ($columns as $column) { $query->orWhere($column, 'LIKE', "%{$search}%"); } }); } return $query; }
/** * This scope eager loads the translations for the default and the fallback locale only. * We can use this as a shortcut to improve performance in our application. * * @param Builder $query */ public function scopeWithTranslation(Builder $query) { $query->with(['translations' => function ($query) { $query->where(function ($query) { $query->where($this->getTranslationsTable() . '.' . $this->getLocaleKey(), $this->locale()); if ($this->useFallback()) { return $query->orWhere($this->getTranslationsTable() . '.' . $this->getLocaleKey(), $this->getFallbackLocale($this->locale()))->orWhere($this->getTranslationsTable() . '.' . $this->getLocaleKey(), $this->getFallbackLocale()); } }); }]); }
/** * Aplicar estrutura de busca em uma BuilderQuery. * * @param Builder $query * @param array $groups */ public function applyQuery(Builder $query, array $groups) { $query->where(function ($query) use($groups) { foreach ($groups as $items) { $query->orWhere(function ($query) use($items) { foreach ($items as $item) { $query->where($item->field, $item->op, $item->value, $item->link); } }); } }); }
/** * @param \Illuminate\Database\Eloquent\Builder|static $query * @param string $keyword * @param boolean $matchAllFields */ public static function scopeSearch($query, $keyword, $matchAllFields = false) { return static::where(function ($query) use($keyword, $matchAllFields) { foreach (static::getSearchFields() as $field) { if ($matchAllFields) { $query->where($field, 'LIKE', "%{$keyword}%"); } else { $query->orWhere($field, 'LIKE', "%{$keyword}%"); } } }); }
private function filter(\Illuminate\Database\Eloquent\Builder $_db) { $_db->take($this->limit); if (Request::get('offset')) { $_db->skip(Request::get('offset')); } if (Request::get('q')) { $_db->where('name', 'like', Request::get('q') . '%'); $_db->orWhere('lastname', 'like', Request::get('q') . '%'); } return $_db; }
/** * Constrain a query to an ability for a specific model. * * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query * @param \Illuminate\Database\Eloquent\Model $model * @return void */ public function scopeForModel($query, Model $model) { $query->where(function ($query) use($model) { $query->where('entity_type', $model->getMorphClass()); $query->where(function ($query) use($model) { $query->whereNull('entity_id'); if ($model->exists) { $query->orWhere('entity_id', $model->getKey()); } }); }); }
/** * Apply a list of filters to an SQL query * * @param Builder $query The query to apply the search to. * @param array $fields The fields and terms to apply to the query. * * @return self */ public static function search(Builder $query, array $fields = []) { array_walk($fields, function ($values, $field) use($query) { $query->where($field, 'LIKE', array_shift($values)); if (count($values) === 0) { return $query; } $query->where(function ($query) use($field, $values) { array_walk($values, function ($value) use($field, $query) { $query->orWhere($field, 'LIKE', $value); }); }); }); return $query; }
/** * Filter query result for a given period * @param EloquentBuilder $query Query * @param \Carboǹ\Carbon|null $after Start of the period * @param \Carboǹ\Carbon|null $before End of the period * @return \Illuminate\Database\Eloquent\Builder Query */ private function buildScopreInPeriode(EloquentBuilder $query, $after, $before) { return $query->where(function (EloquentBuilder $query) use($after, $before) { if ($after instanceof Carbon) { $query->where('date', '>=', $after); } if ($before instanceof Carbon) { $query->where(function (EloquentBuilder $query) use($after, $before) { $query->where('date', '<=', $before); if ($after instanceof Carbon === false) { $query->orWhere('date', null); } }); } }); }
/** * @param EloquentBuilder $query */ private function relatedEventsOutcome(EloquentBuilder $query) { $query->orWhere(function (EloquentBuilder $query) { $query->where('entity_type', 'App\\Outcome')->whereIn('entity_id', function (QueryBuilder $query) { $query->select('id')->from('outcomes')->whereIn('envelope_id', function (QueryBuilder $query) { $query->select('id')->from('envelopes')->where('account_id', $this->id); }); }); }); }
/** * Filters a relationship options query by a search term * * @param mixed $term * @param \Illuminate\Database\Query\Builder $query * @param \Frozennode\Administrator\Fields\Field $fieldObject * @param array $selectedItems * @param string $relatedKeyTable */ public function filterBySearchTerm($term, EloquentBuilder &$query, Field $fieldObject, array $selectedItems, $relatedKeyTable) { if ($term) { $query->where(function ($query) use($term, $fieldObject) { foreach ($fieldObject->getOption('search_fields') as $search) { $query->orWhere($this->db->raw($search), 'LIKE', '%' . $term . '%'); } }); //exclude the currently-selected items if there are any if (count($selectedItems)) { $query->whereNotIn($relatedKeyTable, $selectedItems); } //set up the limits $query->take($fieldObject->getOption('num_options') + count($selectedItems)); } }
/** * @param Builder $builder * @param $field * @param $value * * @return Builder|static */ public function scopeOrWhereIlike(Builder $builder, $field, $value) { return $builder->orWhere($field, 'ilike', "%{$value}%"); }
/** * Add an OR WHERE to the query according to the specified "tag" * * A "tag" follows this format: {filedir_1}your_file.jpg * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $tag * @return \Illuminate\Database\Eloquent\Builder */ public function scopeFileTag(Builder $query, $tag) { if (!preg_match('#^{filedir_(\\d+)}(.*)$#', $tag, $match)) { return $query; } $filedir = $match[1]; $filename = $match[2]; return $query->orWhere(function ($query) use($filename, $filedir) { return $query->where('file_name', $filename)->where('upload_location_id', $filedir); }); }
/** * @param Builder $query * @param Request $request * @param array $columns * @param array $routes * @param array $confirm_config * @param array $search_config * @param bool $enable_lines_choice * @param int|null $lines_number * @return mixed */ public function prepare(Builder $query, Request $request, array $columns, array $routes, array $confirm_config = [], array $search_config = [], bool $enable_lines_choice = false, int $lines_number = null) { // we set the default lines number $default_lines = isset($lines_number) ? $lines_number : config('tablelist.default.lines'); // we set the default sort by and sort dir $possible_directions = ['asc', 'desc']; $default_sort_dir = true; $default_sort_by = null; foreach ($columns as $key => $column) { if (isset($column['sort_by_default']) && in_array($column['sort_by_default'], $possible_directions)) { if (isset($column['sort_by'])) { $default_sort_by = $column['sort_by']; } $default_sort_dir = $column['sort_by_default'] === 'asc' ? true : false; break; } } // if there is no default sort in the given config, // we set the first column with a sort by attribute as the default sort $default_sort_by = isset($default_sort_by) ? $default_sort_by : array_first($columns, function ($key, $column) { return isset($column['sort_by']); })['sort_by']; // we set the default search $default_search = ''; // we set the nav data accordingly to the inputs $tableListData['lines'] = $request->get('lines', $default_lines); $tableListData['sort_by'] = $request->get('sort-by', $default_sort_by); $tableListData['search'] = $request->get('search', $default_search); // we check the inputs $validator = Validator::make($tableListData, ['lines' => 'required|numeric', 'search' => 'alpha_dash']); // if errors are found if ($validator->fails()) { // we use the default values $tableListData['lines'] = $default_lines; $tableListData['sort_by'] = $default_sort_by; $tableListData['search'] = $default_search; } // we order the request $tableListData['sort_dir'] = $request->get('sort-dir', $default_sort_dir); $query_sort_dir = $tableListData['sort_dir'] ? 'asc' : 'desc'; if ($tableListData['sort_by']) { $query->orderBy($tableListData['sort_by'], $query_sort_dir); } // we search into the request $tableListData['search_config'] = $search_config; if ($tableListData['search'] = $request->get('search')) { // we search only the configured field foreach ($tableListData['search_config'] as $key => $searched_field) { if ($key > 0) { $query->orWhere($searched_field['database'], 'like', '%' . $tableListData['search'] . '%'); } else { $query->where($searched_field['database'], 'like', '%' . $tableListData['search'] . '%'); } } } // if the number of lines to show is defined $tableListData['enable_lines_choice'] = $enable_lines_choice; if ($tableListData['enable_lines_choice']) { // we paginate the results $pagination = $query->paginate($tableListData['lines']); // we add the lines and search inputs to the pagination url $pagination->appends(['lines' => $tableListData['lines'], 'search' => $tableListData['search']]); $tableListData['pagination'] = $pagination; // we generate the table nav infos $tableListData['nav_infos'] = $this->tableNavStatus($pagination); } else { $tableListData['pagination'] = $query->get(); } // we put the columns into the table list data $tableListData['columns'] = $columns; // we put the route into the table list data $tableListData['routes'] = $routes; // we activate the confirm modal for the entity removal if (isset($routes['destroy']) && !empty($routes['destroy']) && !empty($confirm_config)) { Modal::confirm($confirm_config); } return $tableListData; }
/** * Constrain a query to an permission for a specific model. * * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query * @param \Illuminate\Database\Eloquent\Model|string $model * @param bool $strict * @return void */ public function scopeForModel($query, $model, $strict = false) { $model = is_string($model) ? new $model() : $model; $query->where(function ($query) use($model, $strict) { $query->where('entity_type', $model->getMorphClass()); $query->where(function ($query) use($model, $strict) { // If the model does not exist, we want to search for blanket permissions // that cover all instances of this model. If it does exist, we only // want to find blanket permissions if we're not using strict mode. if (!$model->exists || !$strict) { $query->whereNull('entity_id'); } if ($model->exists) { $query->orWhere('entity_id', $model->getKey()); } }); }); }
/** * Scope a query to perform a very basic search of pages. * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $search * @param array $columns * @return \Illuminate\Database\Eloquent\Builder */ public function scopeSearch($query, $search, $columns = []) { if (empty($columns)) { $columns = ['title', 'subtitle', 'body']; } // Check if query is surrounded by double or single quotes if (preg_match('/^(["\']).*\\1$/m', $search) !== false) { // Remove quotes $search = str_replace('"', "", $search); $search = str_replace("'", "", $search); } else { // If not surrounded by quotes, replace all spaces with wildcard $search = str_replace(' ', '%', $search); } return $query->where(function ($query) use($columns, $search) { foreach ($columns as $column) { $query->orWhere($column, 'LIKE', "%{$search}%"); } }); }
/** * Adds scope to get a list of translated attributes, using the current locale. * Example usage: Country::scopeListsTranslations('name')->get()->toArray() * Will return an array with items: * [ * 'id' => '1', // The id of country * 'name' => 'Griechenland' // The translated name * ]. * @param Builder $query * @param $translationField */ public function scopeListsTranslations(Builder $query, $translationField) { $withFallback = $this->useFallback(); $query->select($this->getTable() . '.' . $this->getKeyName(), $this->getTranslationsTable() . '.' . $translationField)->leftJoin($this->getTranslationsTable(), $this->getTranslationsTable() . '.' . $this->getRelationKey(), '=', $this->getTable() . '.' . $this->getKeyName())->where($this->getTranslationsTable() . '.' . $this->getLocaleKey(), app()->getLocale()); if ($withFallback) { $query->orWhere(function (Builder $q) { $q->where($this->getTranslationsTable() . '.' . $this->getLocaleKey(), $this->getFallbackLocale())->whereNotIn($this->getTranslationsTable() . '.' . $this->getRelationKey(), function (QueryBuilder $q) { $q->select($this->getTranslationsTable() . '.' . $this->getRelationKey())->from($this->getTranslationsTable())->where($this->getTranslationsTable() . '.' . $this->getLocaleKey(), app()->getLocale()); }); }); } }
/** * Build wildcard query filter by keyword. * * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query * @param string $field * @param array $keyword * @param string $group * * @return void */ protected function buildWildcardQueryFilterWithKeyword($query, $field, array $keyword = [], $group = 'where') { $callback = function ($query) use($field, $keyword) { foreach ($keyword as $key) { $query->orWhere($field, 'LIKE', $key); } }; $query->{$group}($callback); }
/** * @param Builder $query * @param string $q * * @return Builder */ protected function performSearch(Builder $query, $q) { $searchFields = $this->getSearchFields(); if (!empty($searchFields)) { $query->where(function (Builder $query) use($q, $searchFields) { foreach ($searchFields as $i => $searchField) { if ($i > 0) { $query->orWhere($searchField, 'LIKE', '%' . $q . '%'); } else { $query->where($searchField, 'LIKE', '%' . $q . '%'); } } }); } return $query; }
/** * Add an "or where" clause to the query. * * @param string $column * @param string $operator * @param mixed $value * @return \Illuminate\Database\Eloquent\Builder|static * @static */ public static function orWhere($column, $operator = null, $value = null) { return \Illuminate\Database\Eloquent\Builder::orWhere($column, $operator, $value); }
/** * @param mixed $query * @param Builder $builder */ public function prepareQuery($query, Builder $builder) { foreach ($this->getSearchFields() as $field) { $builder->orWhere($field, 'like', "%{$query}%"); } }