/** * @param QueryBuilder $builder * @param $data * * @return void */ public function applyFilterConstraint(QueryBuilder $builder, $data) { if (empty($data)) { return; } $builder->whereIn($this->relation->getForeignKey(), explode(',', $data)); }
/** * Find by conditions in request * * @param Builder $query * @param array|null $request * @return \Illuminate\Database\Query\Builder */ public function scopeFindByRequest($query, $request = NULL) { if (is_null($request)) { $request = Input::all(); } $findable = isset($this->findable) ? $this->findable : []; foreach ($request as $field => $value) { if (!in_array($field, $findable)) { continue; } if ($field == 'tag') { if (isset($request['tag_search']) && $request['tag_search'] == 'any') { $query->withAnyTag($value); } else { $query->withAllTags($value); } continue; } if (is_array($value)) { $query->whereIn($field, $value); } elseif (is_scalar($value)) { $query->where($field, '=', $value); } } }
/** * Find a model by its primary key. * * @param array $id * @param array $columns * @return \Illuminate\Database\Eloquent\Model|Collection|static */ public function findMany($id, $columns = array('*')) { if (empty($id)) { return $this->model->newCollection(); } $this->query->whereIn($this->model->getQualifiedKeyName(), $id); return $this->get($columns); }
/** * 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 'between': $this->operator->whereBetween($field, $val); break; case 'raw': $this->operator->whereRaw($val); break; default: $this->operator->where($field, $op, $val); } } return $this; }
/** * * Delete numbers of notifications equals * to the number passing as 2 parameter of * the current user * * @param $user_id int * @param $entity * @param $number int * @param $order string * @return int * @throws \Exception */ public function deleteLimit($user_id, $entity, $number, $order) { $notifications_ids = $this->notification->wherePolymorphic($user_id, $entity)->orderBy('id', $order)->select('id')->limit($number)->lists('id'); if (count($notifications_ids) == 0) { return false; } return $this->notification->whereIn('id', $notifications_ids)->delete(); }
/** * Add a where clause on the primary key to the query. * * @param mixed $id * @return $this */ public function whereKey($id) { if (is_array($id)) { $this->query->whereIn($this->model->getQualifiedKeyName(), $id); return $this; } return $this->where($this->model->getQualifiedKeyName(), '=', $id); }
/** * Extend of the eloquent whereIn function without error when empty array is given */ public function whereIn($column, $values, $boolean = 'and', $not = false) { if (empty($values)) { $this->whereRaw(0); } else { parent::whereIn($column, $values, $boolean, $not); } return $this; }
/** * Lấy resources theo $level * * @param \Illuminate\Database\Query\Builder $query * @param int $level * * @return \Illuminate\Database\Query\Builder */ public function scopeLevel($query, $level) { // Điều kiện: resources đang ở level $level $query->where("{$this->table}.level", '=', $level); if (!user()->inAdminGroup()) { switch ($level) { case ResourceLevel::LEVEL_CANHAN: // Điều kiện: resources do chính user() tạo $query->where("{$this->table}.user_id", '=', user('id')); break; case ResourceLevel::LEVEL_DONVI: // Điều kiện: resources của các user khác cùng đơn vị do user() làm thủ trưởng if (user()->isGroupManager() && ($ids = user()->group->users->lists('id', 'username')->forget(user('username'))->all())) { $query->whereIn("{$this->table}.user_id", $ids); } else { $query->whereRaw('1=0'); } break; case ResourceLevel::LEVEL_COQUAN: // Điều kiện: resources thuộc các categories user()-group được phép quản lý if (user()->isGroupManager() && ($ids = user()->group->categories->lists('id')->all())) { $query->whereIn("{$this->table}.category_id", $ids); } else { $query->whereRaw('1=0'); } break; case ResourceLevel::LEVEL_BGH: // Điều kiện: là thủ trưởng bgh if (!user()->inBgh()) { $query->whereRaw('1=0'); } break; default: $query->whereRaw('1=0'); } } return $query; }
/** * Add a "where in" clause to the query. * Split one WHERE IN clause into multiple clauses each * with up to 1000 expressions to avoid ORA-01795 * * @param string $column * @param mixed $values * @param string $boolean * @param bool $not * @return \Illuminate\Database\Query\Builder|\Yajra\Oci8\Query\OracleBuilder */ public function whereIn($column, $values, $boolean = 'and', $not = false) { $type = $not ? 'NotIn' : 'In'; if (count($values) > 1000) { $chunks = array_chunk($values, 1000); return $this->where(function ($query) use($column, $chunks, $type) { $firstIteration = true; foreach ($chunks as $ch) { $sqlClause = $firstIteration ? 'where' . $type : 'orWhere' . $type; $query->{$sqlClause}($column, $ch); $firstIteration = false; } }, null, null, $boolean); } return parent::whereIn($column, $values, $boolean, $not); }
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; }
/** * Modify a Builder object. Changes here can be nested * @param \Illuminate\Database\Query\Builder $query * @return \Illuminate\Database\Query\Builder $query */ public function apply(\Illuminate\Database\Query\Builder $query) { $group_results = DB::table('tmp_group_user_count')->select('group_id'); $query->whereIn('test_user_groups.group_id', $group_results->all()); return $query; }
/** * 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); } } } } } }
/** * Take user by roles. * * @param \Illuminate\Database\Query\Builder $query * @param string|array $roles * * @return \Illuminate\Database\Query\Builder */ public function scopeWhichRoles($query, $roles) { return $query->whereHas('roles', function ($query) use($roles) { $roles = is_array($roles) ? $roles : [$roles]; $query->whereIn('name', $roles); }); }
public function whereIn($column, $values, $boolean = 'and', $not = false) { $column = is_string($column) ? snake_case($column) : $column; return parent::whereIn($column, $values, $boolean, $not); // TODO: Change the autogenerated stub }
/** * Add where to query builder * @param \Illuminate\Database\Query\Builder $query * @param array $subentity = null only add wheres with this subeneity */ protected function addWhereQuery($query, $subentity = null) { // Ex with both filter and where: select * from `dms` where (`key` like ? or `name` like ?) and `disabled` = ? if (isset($this->where)) { foreach ($this->where as $where) { #$table = $where['table']; $column = $where['column']; $operator = $where['operator']; $value = $where['value']; if ($operator == 'in') { #$query->whereIn("$table.$column", $value); $query->whereIn($column, $value); } elseif ($operator == 'null') { if ($value) { $query->whereNull($column); } else { $query->whereNotNull($column); } } else { #$query->where("$table.$column", $operator, $value); $query->where($column, $operator, $value); } } } }
/** * Constrain a query to having the given name. * * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query * @return string|array $name * @return bool $strict * @return void */ public function scopeByName($query, $name, $strict = false) { $names = (array) $name; if (!$strict) { $names[] = '*'; } $query->whereIn("{$this->table}.name", $names); }
/** * delete resource * * @param array|integer $id * * @return boolean */ public function destroy($id) { return $this->query->whereIn('id', $id)->delete(); }
protected function process() { $this->method = 'GET'; //database save switch ($this->action) { case "search": // prepare the WHERE clause foreach ($this->fields as $field) { $field->getValue(); $field->getNewValue(); $value = $field->new_value; //query scope $query_scope = $field->query_scope; $query_scope_params = $field->query_scope_params; if ($query_scope) { if (is_a($query_scope, '\\Closure')) { array_unshift($query_scope_params, $value); array_unshift($query_scope_params, $this->query); $this->query = call_user_func_array($query_scope, $query_scope_params); } elseif (isset($this->model) && method_exists($this->model, "scope" . $query_scope)) { $query_scope = "scope" . $query_scope; array_unshift($query_scope_params, $value); array_unshift($query_scope_params, $this->query); $this->query = call_user_func_array([$this->model, $query_scope], $query_scope_params); } continue; } //detect if where should be deep (on relation) $deep_where = false; if (isset($this->model) && $field->relation != null) { $rel_type = get_class($field->relation); if (is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\HasOne') || is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\HasMany') || is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\BelongsTo') || is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\BelongsToMany')) { if (is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\BelongsTo') and in_array($field->type, array('select', 'radiogroup', 'autocomplete'))) { $deep_where = false; } else { $deep_where = true; } } } if ($value != "" or is_array($value) and count($value)) { if (strpos($field->name, "_copy") > 0) { $name = substr($field->db_name, 0, strpos($field->db_name, "_copy")); } else { $name = $field->db_name; } //$value = $field->value; if ($deep_where) { //exception for multiple value fields on BelongsToMany if (is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\BelongsToMany') || is_a($field->relation, 'Illuminate\\Database\\Eloquent\\Relations\\BelongsTo') and in_array($field->type, array('tags', 'checks', 'multiselect'))) { $values = explode($field->serialization_sep, $value); if ($field->clause == 'wherein') { $this->query = $this->query->whereHas($field->rel_name, function ($q) use($field, $values) { $q->whereIn($field->rel_fq_key, $values); }); } if ($field->clause == 'where') { foreach ($values as $v) { $this->query = $this->query->whereHas($field->rel_name, function ($q) use($field, $v) { $q->where($field->rel_fq_key, '=', $v); }); } } continue; } switch ($field->clause) { case "like": $this->query = $this->query->whereHas($field->rel_name, function ($q) use($field, $value) { $q->where($field->rel_field, 'LIKE', '%' . $value . '%'); }); break; case "orlike": $this->query = $this->query->orWhereHas($field->rel_name, function ($q) use($field, $value) { $q->where($field->rel_field, 'LIKE', '%' . $value . '%'); }); break; case "where": $this->query = $this->query->whereHas($field->rel_name, function ($q) use($field, $value) { $q->where($field->rel_field, $field->operator, $value); }); break; case "orwhere": $this->query = $this->query->orWhereHas($field->rel_name, function ($q) use($field, $value) { $q->where($field->rel_field, $field->operator, $value); }); break; case "wherebetween": $values = explode($field->serialization_sep, $value); $this->query = $this->query->whereHas($field->rel_name, function ($q) use($field, $values) { if ($values[0] != '' and $values[1] == '') { $q->where($field->rel_field, ">=", $values[0]); } elseif ($values[0] == '' and $values[1] != '') { $q->where($field->rel_field, "<=", $values[1]); } elseif ($values[0] != '' and $values[1] != '') { //we avoid "whereBetween" because a bug in laravel 4.1 $q->where(function ($query) use($field, $values) { return $query->where($field->rel_field, ">=", $values[0])->where($field->rel_field, "<=", $values[1]); }); } }); break; case "orwherebetween": $values = explode($field->serialization_sep, $value); $this->query = $this->query->orWhereHas($field->rel_name, function ($q) use($field, $values) { if ($values[0] != '' and $values[1] == '') { $q->orWhere($field->rel_field, ">=", $values[0]); } elseif ($values[0] == '' and $values[1] != '') { $q->orWhere($field->rel_field, "<=", $values[1]); } elseif ($values[0] != '' and $values[1] != '') { //we avoid "whereBetween" because a bug in laravel 4.1 $q->orWhere(function ($query) use($field, $values) { return $query->where($field->rel_field, ">=", $values[0])->where($field->rel_field, "<=", $values[1]); }); } }); break; } //not deep, where is on main entity } else { switch ($field->clause) { case "like": $this->query = $this->query->where($name, 'LIKE', '%' . $value . '%'); break; case "orlike": $this->query = $this->query->orWhere($name, 'LIKE', '%' . $value . '%'); break; case "where": $this->query = $this->query->where($name, $field->operator, $value); break; case "orwhere": $this->query = $this->query->orWhere($name, $field->operator, $value); break; case "wherein": $this->query = $this->query->whereIn($name, explode($field->serialization_sep, $value)); break; case "wherebetween": $values = explode($field->serialization_sep, $value); if (count($values) == 2) { if ($values[0] != '' and $values[1] == '') { $this->query = $this->query->where($name, ">=", $values[0]); } elseif ($values[0] == '' and $values[1] != '') { $this->query = $this->query->where($name, "<=", $values[1]); } elseif ($values[0] != '' and $values[1] != '') { //we avoid "whereBetween" because a bug in laravel 4.1 $this->query = $this->query->where(function ($query) use($name, $values) { return $query->where($name, ">=", $values[0])->where($name, "<=", $values[1]); }); } } break; case "orwherebetween": $values = explode($field->serialization_sep, $value); if (count($values) == 2) { if ($values[0] != '' and $values[1] == '') { $this->query = $this->query->orWhere($name, ">=", $values[0]); } elseif ($values[0] == '' and $values[1] != '') { $this->query = $this->query->orWhere($name, "<=", $values[1]); } elseif ($values[0] != '' and $values[1] != '') { //we avoid "whereBetween" because a bug in laravel 4.1 $this->query = $this->query->orWhere(function ($query) use($name, $values) { return $query->where($name, ">=", $values[0])->where($name, "<=", $values[1]); }); } } break; } } } } // dd($this->query->toSql()); break; case "reset": $this->process_status = "show"; return true; break; default: return false; } }
/** * 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); } } } } } }
/** * Add a "where in" clause to the query. * * @param string $column * @param mixed $values * @param string $boolean * @param bool $not * @return $this * @static */ public static function whereIn($column, $values, $boolean = 'and', $not = false) { return \Illuminate\Database\Query\Builder::whereIn($column, $values, $boolean, $not); }
/** * Find a model by its primary key. * * @param array $id * @param array $columns * @return \Illuminate\Database\Eloquent\Model|Collection|static */ public function findMany($id, $columns = array('*')) { $this->query->whereIn($this->model->getKeyName(), $id); return $this->get($columns); }
/** * @param EloquentBuilder|QueryBuilder|Model $query * @param string $column * @param EloquentBuilder|QueryBuilder|Model $subQuery * @param string $subQueryColumn * * @return QueryBuilder */ public function scopeWhereInSubQuery($query, $column, $subQuery, $subQueryColumn) { if (!Str::contains($column, '.')) { /** @var Model|BetterEloquentTrait $model */ $model = $query->getModel(); $column = $model->getField($column); } $subQuery = $subQuery->toSubQuery($subQueryColumn, true); return $query->whereIn($column, $subQuery); }
/** * Takes the supplied $selectedItems mixed value and formats it to a usable array * * @param \Illuminate\Database\Query\Builder $query * @param array $selectedItems * @param \Frozennode\Administrator\Fields\Field $fieldObject * @param string $relatedKeyTable * * @return array */ public function filterQueryBySelectedItems(QueryBuilder &$query, array $selectedItems, Field $fieldObject, $relatedKeyTable) { $query->whereIn($relatedKeyTable, $selectedItems); //if this is a BelongsToMany and a sort field is set, order it by the sort field if ($fieldObject->getOption('multiple_values') && $fieldObject->getOption('sort_field')) { $query->orderBy($fieldObject->getOption('sort_field')); } //otherwise order it by the name field else { $query->orderBy($fieldObject->getOption('name_field')); } }