/** * Store settings in the database. */ public function save() { if ($this->unsaved) { $all = $this->getData(); $data = array_dot($all); foreach ($data as $key => $value) { $this->options->updateOrCreate(compact('key'), compact('key', 'value')); } $this->options->whereNotIn('key', array_keys($data))->delete(); $this->unsaved = false; } }
/** * like wheres function ,call by internal * @param array $conds * @return $this */ protected function _wheres($conds) { foreach ($conds as $field => $opAndVal) { if (is_null($opAndVal)) { $opAndVal = [null]; } $opAndVal = (array) $opAndVal; $op = strtolower(count($opAndVal) == 1 ? '=' : $opAndVal[0]); $val = last($opAndVal); $field = str_contains($field, '.') ? $field : $this->_table . '.' . $field; switch ($op) { case 'in': if (count($val) == 1) { $this->_operator->where($field, '=', $val[0]); } else { $this->_operator->whereIn($field, $val); } break; case 'notin': if (count($val) == 1) { $this->_operator->where($field, '<>', $val[0]); } else { $this->_operator->whereNotIn($field, $val); } break; case 'between': $this->_operator->whereBetween($field, $val); break; case 'notbetween': $this->_operator->whereNotBetween($field, $val); break; case 'null': if ($val) { $this->_operator->whereNull($field); } else { $this->_operator->whereNotNull($field); } break; case 'raw': $this->_operator->whereRaw($val); break; default: $this->_operator->where($field, $op, $val); } } return $this; }
public function fetchSelectedItems(QueryBuilder $builder) { $self = $this; $this->filters->items[] = -1; //faço a busca de acordo com a palavra pesquisada, caso tenha uma: if ($this->filters->searchString) { if (count($this->filters->columns) > 0) { $builder->where(function ($q) use($self, $builder) { foreach ($this->filters->columns as $column) { if (!$column->bSearchable) { continue; } $builder->orWhereRaw($column->name . " LIKE '%" . $self->filters->searchString . "%'"); } }); } } if ($this->filters->checkedAll == 1) { $builder->whereNotIn($this->filters->idField, $this->filters->items); return $builder; } $builder->whereIn($this->filters->idField, $this->filters->items); return $builder; }
/** * Add a "where not in" clause to the query. * * @param string $column * @param mixed $values * @param string $boolean * @return \Illuminate\Database\Query\Builder|static * @static */ public static function whereNotIn($column, $values, $boolean = 'and') { return \Illuminate\Database\Query\Builder::whereNotIn($column, $values, $boolean); }
/** * Parse the remaining filter params * * @param array $filterParams * @return void */ protected function parseFilter($filterParams) { $supportedPostfixes = ['st' => '<', 'gt' => '>', 'min' => '>=', 'max' => '<=', 'lk' => 'LIKE', 'not-lk' => 'NOT LIKE', 'in' => 'IN', 'not-in' => 'NOT IN', 'not' => '!=']; $supportedPrefixesStr = implode('|', $supportedPostfixes); $supportedPostfixesStr = implode('|', array_keys($supportedPostfixes)); foreach ($filterParams as $filterParamKey => $filterParamValue) { $keyMatches = []; //Matches every parameter with an optional prefix and/or postfix //e.g. not-title-lk, title-lk, not-title, title $keyRegex = '/^(?:(' . $supportedPrefixesStr . ')-)?(.*?)(?:-(' . $supportedPostfixesStr . ')|$)/'; preg_match($keyRegex, $filterParamKey, $keyMatches); if (!isset($keyMatches[3])) { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NULL'; } else { $comparator = '='; } } else { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NOT NULL'; } else { $comparator = $supportedPostfixes[$keyMatches[3]]; } } $column = $keyMatches[2]; if ($comparator == 'IN') { $values = explode(',', $filterParamValue); $this->query->whereIn($column, $values); } else { if ($comparator == 'NOT IN') { $values = explode(',', $filterParamValue); $this->query->whereNotIn($column, $values); } else { $values = explode('|', $filterParamValue); if (count($values) > 1) { $this->query->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') { $this->query->whereNull($column, 'and', $comparator == 'NOT NULL'); } else { $this->query->where($column, $comparator, $value); } } } } } }
/** * Parse the remaining filter params * * @param array $filterParams * @return void */ protected function parseFilter($filterParams) { $supportedPostfixes = ['st' => '<', 'gt' => '>', 'min' => '>=', 'max' => '<=', 'lk' => 'LIKE', 'not-lk' => 'NOT LIKE', 'in' => 'IN', 'not-in' => 'NOT IN', 'not' => '!=']; $supportedPrefixesStr = implode('|', $supportedPostfixes); $supportedPostfixesStr = implode('|', array_keys($supportedPostfixes)); foreach ($filterParams as $filterParamKey => $filterParamValue) { $keyMatches = []; //Matches every parameter with an optional prefix and/or postfix //e.g. not-title-lk, title-lk, not-title, title $keyRegex = '/^(?:(' . $supportedPrefixesStr . ')-)?(.*?)(?:-(' . $supportedPostfixesStr . ')|$)/'; preg_match($keyRegex, $filterParamKey, $keyMatches); if (!isset($keyMatches[3])) { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NULL'; } else { $comparator = '='; } } else { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NOT NULL'; } else { $comparator = $supportedPostfixes[$keyMatches[3]]; } } $column = $keyMatches[2]; //resolve a relation->relation_column if we are using an eloquent builder. if (strpos($column, '->') !== false && $this->isEloquentBuilder) { $model = $this->builder->getModel(); //get the relation and the column in the relation $relationComponents = explode('->', $column); if (!method_exists($model, $relationComponents[0])) { throw new ApiHandlerException('UnknownResourceRelation', ['relation' => $relationComponents[0]]); } $relation = call_user_func([$model, $relationComponents[0]]); $relationColumn = $relationComponents[1]; $relationType = $this->getRelationType($relation); if ($relationType === 'BelongsTo') { $firstKey = $relation->getQualifiedForeignKey(); $secondKey = $relation->getQualifiedOtherKeyName(); } else { if ($relationType === 'HasMany' || $relationType === 'HasOne') { $firstKey = $relation->getQualifiedParentKeyName(); $secondKey = $relation->getForeignKey(); } else { if ($relationType === 'BelongsToMany') { $firstKey = $relation->getQualifiedParentKeyName(); $secondKey = $relation->getRelated()->getQualifiedKeyName(); } } } //get the table to join to $joinTable = $relation->getRelated()->getTable(); //do the join $this->query->join($joinTable, $firstKey, '=', $secondKey); //modify the $column name and continue parsing. $column = $joinTable . '.' . $relationColumn; $this->query->addSelect($model->getTable() . '.*'); } //should we be using an eloquent builder, append the table name to every column to differentiate from joined columns. if ($this->isEloquentBuilder) { if (strpos($column, '.') === false) { $column = $this->builder->getModel()->getTable() . '.' . $column; } } if ($comparator == 'IN') { $values = explode(',', $filterParamValue); $this->query->whereIn($column, $values); } else { if ($comparator == 'NOT IN') { $values = explode(',', $filterParamValue); $this->query->whereNotIn($column, $values); } else { $values = explode('|', $filterParamValue); if (count($values) > 1) { $this->query->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') { $this->query->whereNull($column, 'and', $comparator == 'NOT NULL'); } else { $this->query->where($column, $comparator, $value); } } } } } }
/** * Filters a relationship options query by a search term * * @param mixed $term * @param \Illuminate\Database\Query\Builder $query * @param array $selectedItems * @param \Frozennode\Administrator\Fields\Field $fieldObject * @param string $relatedKeyTable */ public function filterBySearchTerm($term, QueryBuilder &$query, Field $fieldObject, array $selectedItems, $relatedKeyTable) { if ($term) { //set up the wheres foreach ($fieldObject->getOption('search_fields') as $search) { $query->where($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 EloquentBuilder|QueryBuilder|Model $query * @param string $column * @param QueryBuilder $subQuery * @param string $subQueryColumn * * @return QueryBuilder */ public function scopeWhereNotInSubQuery($query, $column, $subQuery, $subQueryColumn) { $subQuery = $subQuery->toSubQuery($subQueryColumn, true); return $query->whereNotIn($column, $subQuery); }