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}%");
}
}
