/** * @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); }); }); }); }
/** * @param Builder $builder * @param Model $model */ public function apply(Builder $builder, Model $model) { $builder->join('post_translations', function ($join) { $join->on('posts.id', '=', 'post_translations.post_id')->where('post_translations.locale', '=', app()->getLocale())->where('post_translations.publish_at', '<', Carbon::now()->format('Y-m-d H:i:s')); }); $builder->select(['posts.*']); }
/** * @param User $actor * @param Builder $query */ public function find(User $actor, Builder $query) { // Hide discussions which have tags that the user is not allowed to see. $query->whereNotExists(function ($query) use($actor) { return $query->select(new Expression(1))->from('discussions_tags')->whereIn('tag_id', Tag::getIdsWhereCannot($actor, 'viewDiscussions'))->where('discussions.id', new Expression('discussion_id')); }); }
public function scopeWithRelatedTranslations(Builder $query, $relation, $andFields = []) { $model = $relation->getRelated(); $withFallback = $model->usePublicFallback(); $translatedAttributes = array_merge($model->translatedAttributes, $andFields); $translatedAttributes = array_map(function ($attribute) use($model) { return $model->getPublicTranslationsTable() . '.' . $attribute . ' AS ' . $model->getPublicTranslationsTable() . '_' . $attribute; }, $translatedAttributes); $query->withTranslations(); $translatedAttributes = array_merge((array) $query->getQuery()->columns, $translatedAttributes); $query->select($translatedAttributes)->leftJoin($model->getPublicTranslationsTable(), $model->getPublicTranslationsTable() . '.' . $model->getRelationKey(), '=', $relation->getQualifiedForeignKey())->where(function ($query) use($model, $withFallback) { $query->where($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->publicLocale()); if ($withFallback) { $query->orWhere(function (Builder $q) use($model) { $q->where($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->getPublicFallbackLocale($model->publicLocale()))->whereNotIn($model->getPublicTranslationsTable() . '.' . $model->getRelationKey(), function (QueryBuilder $q) use($model) { $q->select($model->getPublicTranslationsTable() . '.' . $model->getRelationKey())->from($model->getPublicTranslationsTable())->where($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->publicLocale()); }); })->orWhere(function (Builder $q) use($model) { $q->where($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->getPublicFallbackLocale())->whereNotIn($model->getPublicTranslationsTable() . '.' . $model->getRelationKey(), function (QueryBuilder $q) use($model) { $q->select($model->getPublicTranslationsTable() . '.' . $model->getRelationKey())->from($model->getPublicTranslationsTable())->where($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->getPublicFallbackLocale($model->publicLocale()))->orWhere($model->getPublicTranslationsTable() . '.' . $model->getLocaleKey(), $model->publicLocale()); }); }); } }); }
/** * Apply the scope to a given Eloquent query builder. * @param Builder $builder * @param Model $model */ public function apply(Builder $builder, Model $model) { $builder->join('product_translations', function ($join) { $join->on('products.id', '=', 'product_translations.product_id')->where('product_translations.locale', '=', app()->getLocale())->where('product_translations.published', '=', 1); }); $builder->select(['products.*']); }
/** * @param $id * @param bool $orFail * @return Model|null * * method attempts to find record with given id */ public function find($id, $orFail = false) { if ($this->cache) { $this->cacheKey = ($this->onlyVisible ? '-' : '+') . ($this->onlyAuthored ? '-' : '+') . "{$this->baseName}.find[{$id}]"; if ($cache = Cache::get($this->cacheKey)) { $this->clearFilters(); return $cache === 'null' ? null : $cache; } } $this->model = new $this->modelClass(); $table = $this->model->getTable(); $this->model = $this->model->newQuery(); $this->model->select("{$table}.*"); $this->prepare($this->model); if ($this->onlyVisible) { $this->filterVisible($this->model); } if ($this->onlyAuthored) { $this->filterAuthored($this->model); } $this->filter($this->model, $this->filters); $this->model->where($table . '.' . $this->identifier, '=', $id); $result = $orFail ? $this->model->firstOrFail() : $this->model->first(); $this->storeToCache($result); $this->clearFilters(); return $result; }
/** * Add the constraints for a relationship count query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQueryForSelfRelation(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $query->from($query->getModel()->getTable() . ' as ' . ($hash = $this->getRelationCountHash())); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($hash . '.' . $this->getPlainForeignKey(), '=', new Expression($key)); }
/** * Exclude from find * * @return \Illuminate\Database\Eloquent\Builder */ public function scopeExcludeFromFind(Builder $query) { if (isset($this->excludedFromFind) == TRUE && is_array($this->excludedFromFind) == TRUE) { return $query->select(array_diff(Schema::getColumnListing($this->table), $this->excludedFromFind)); } else { return $query; } }
/** * Store the result on cache forever * * @param array $columns * @return mixed */ public function rememberForever(array $columns = ['*']) { $this->applyCriteria(); $this->builder->select($columns); return Cache::rememberForever($this->generateCacheKey(), function () use($columns) { return $this->all($columns); }); }
/** * Attach the name of the table to each column of the select if possible * * @param array $columns * @return mixed */ public function select($columns = ['*']) { $columns = !is_array($columns) ? [$columns] : $columns; foreach ($columns as $key => $column) { $columns[$key] = $this->getModelTableColumn($column); } return parent::select($columns); }
/** * Returns the count for a countable parameter, given the query provided * * @param string $name * @param EloquentBuilder $query * @param CountableFilterInterface $filter * @return array */ public function count($name, $query, CountableFilterInterface $filter) { // if the columnname is not set, assume an id field based on a table name $columnName = empty($this->columnName) ? $name : $this->columnName; if (!$this->includeEmpty) { $query->whereNotNull($columnName); } return $query->select("{$columnName} AS {$this->columnAlias}", DB::raw("{$this->countRaw} AS {$this->countAlias}"))->groupBy($columnName)->pluck($this->countAlias, $this->columnAlias); }
public function excludeCriterion(QueryBuilder $query, $columns) { $columns = static::getArgumentsArray($columns, func_get_args()); // Get the attributes of the model and filter out all but specified $schema = $query->getQuery()->getConnection()->getSchemaBuilder(); $allColumns = $schema->getColumnListing($query->getModel()->getTable()); $difference = array_diff($allColumns, $columns); return $query->select($difference); }
/** * @param EloquentBuilder $query * @param EloquentBuilder $parent * @param array $columns * * @return mixed */ public function getRelationQuery(EloquentBuilder $query, EloquentBuilder $parent, $columns = ['*']) { $query->select($columns); $table = $query->getModel()->getTable(); $query->from($table . ' as ' . ($hash = $this->getRelationCountHash())); $grammar = $query->getQuery()->getGrammar(); $table = $grammar->wrapTable($table); $hash = $grammar->wrapTable($hash); $lft = $grammar->wrap($this->parent->getLftName()); $rgt = $grammar->wrap($this->parent->getRgtName()); return $query->whereRaw("{$hash}.{$lft} between {$table}.{$lft} + 1 and {$table}.{$rgt}"); }
/** * @param Builder $query * @param $filters * @param $currentUserId */ public function scopeFilterByAdminGrid(Builder $query, $filters, $currentUserId) { $query->select([User::getTableName() . '.*', 'up.*'])->join(Profile::getTableName() . ' AS up', 'up.user_id', '=', User::getTableName() . '.id', 'right'); foreach ($filters as $filterKey => $filterValue) { if (empty($filterValue)) { continue; } switch ($filterKey) { case 'fname': $query->where('fname', 'LIKE', '%' . $filterValue . '%'); break; case 'email': $query->where('email', 'LIKE', '%' . $filterValue . '%'); break; } } $query->where('id', '!=', $currentUserId); }
private function buildQuery() { $this->query = app(get_class($this->model)); if (!empty($this->fields)) { $this->query = $this->query->select($this->fields); } if (!empty($this->relations)) { $this->relations = array_unique($this->relations); $this->query = $this->query->with($this->relations); } if (!empty($this->per_page)) { $this->query = $this->query->take($this->per_page); } if (count($this->conditions)) { foreach ($this->conditions as $condition) { $this->query = $this->query->where($condition['column'], $condition['operator'], $condition['value'], $condition['boolean']); } } }
/** * Add the constraints for a relationship count query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $query->from($this->related->getTable() . ' as ' . ($hash = $this->getRelationCountHash())); $this->related->setTable($hash); $this->setJoin($query); return parent::getRelationCountQuery($query, $parent); }
/** * Add the constraints for a relationship count query. * * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query * @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $parent * * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder */ public function getRelationCountQuery(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $one = $this->getRelated()->getQualifiedKeyName(); $prev = $this->getForeignKey($this->getRelated()); $alias = null; foreach ($this->models as $model) { if ($this->getParent()->getTable() == $model->getTable()) { $alias = $model->getTable() . '_' . time(); $other = $alias . '.' . $prev; $query->leftJoin(new Expression($model->getTable() . ' as ' . $alias), $one, '=', $other); } else { $other = $model->getTable() . '.' . $prev; $query->leftJoin($model->getTable(), $one, '=', $other); } $prev = $this->getForeignKey($model); $one = $model->getQualifiedKeyName(); } $key = $this->wrap($this->getQualifiedParentKeyName()); $query->where(new Expression($alias . '.' . $this->getParent()->getKeyName()), '=', new Expression($key)); return $query; }
/** * Add the constraints for a relationship query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @param array|mixed $columns * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationQueryForSelfJoin(Builder $query, Builder $parent, $columns = ['*']) { $query->select($columns); $query->from($this->table . ' as ' . ($hash = $this->getRelationCountHash())); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($hash . '.' . $this->foreignKey, '=', new Expression($key)); }
/** * Scope a query to only include events matching search term. * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $text * @return \Illuminate\Database\Eloquent\Builder */ public function scopeFilterKeyword($query, $keyword) { $query->select('events.*'); if (!$this->isJoined($query, 'event_types')) { $query->join('event_types', 'events.type_id', '=', 'event_types.id'); } if (!$this->isJoined($query, 'event_venues')) { $query->join('event_venues', 'events.venue_id', '=', 'event_venues.id'); } $query->where(function ($query) use($keyword) { $query->where('events.title', 'LIKE', '%' . $keyword . '%')->orWhere('events.description', 'LIKE', '%' . $keyword . '%')->orWhere('event_venues.name', 'LIKE', '%' . $keyword . '%')->orWhere('event_venues.address', 'LIKE', '%' . $keyword . '%')->orWhere('event_types.name', 'LIKE', '%' . $keyword . '%'); }); return $query; }
/** * Add the constraints for a relationship query. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @param array|mixed $columns * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationQuery(Builder $query, Builder $parent, $columns = ['*']) { $query->select($columns); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($this->getHasCompareKey(), 'exists', true); }
/** * Scope for getting pages with the current version. * * This doesn't work as a global scope as changing the select columns breaks queries which add select columns. * * e.g. finding pages by related tags. * * @param Builder $query * * @return Builder */ public function scopeCurrentVersion(Builder $query) { $subquery = $this->getCurrentVersionQuery(); return $query->select('version.*')->addSelect('version.id as version:id')->addSelect('pages.*')->join(DB::raw('(' . $subquery->toSql() . ') as v2'), 'pages.id', '=', 'v2.page_id')->mergeBindings($subquery)->join('page_versions as version', function (JoinClause $join) { $join->on('pages.id', '=', 'version.page_id')->on('v2.id', '=', 'version.id'); }); }
/** * Recently visited scope * * @param Builder $query * @param int|null $limit * @return Builder */ public function scopeRecentlyVisited(Builder $query, $limit = null) { $query->select(\DB::raw('nodes.*, MAX(node_site_view.site_view_id) as `aggregate`'))->join('node_site_view', 'nodes.id', '=', 'node_site_view.node_id')->orderBy('aggregate', 'desc')->groupBy('node_site_view.node_id'); if ($limit) { $query->limit($limit); } return $query; }
/** * Scope a query to only include activities that have no sign-up and not passed the current date. * * @param \Illuminate\Database\Eloquent\Builder $query the query to activities to be scoped * @return \Illuminate\Database\Eloquent\Builder */ public function scopeUnfilled($query) { $query->select('activities.*')->leftJoin('tasks', 'activities.activity_id', '=', 'tasks.activity_id')->where(function ($query) { $query->whereNull('tasks.approval')->orWhere(function ($orQuery) { $orQuery->whereNotIn('tasks.approval', ['pending', 'approved']); }); })->upcoming(); }
/** * Add the constraints for a relationship count query. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQuery(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $otherKey = $this->wrap($query->getModel()->getTable() . '.' . $this->otherKey); return $query->where($this->getQualifiedForeignKey(), '=', new Expression($otherKey)); }
/** * Add the constraints for a relationship count query. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQuery(Builder $query, Builder $parent) { $foreignKey = $this->getHasCompareKey(); return $query->select($this->getHasCompareKey())->where($this->getHasCompareKey(), 'exists', true); }
/** * Add the constraints for a relationship query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @param array|mixed $columns * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationQueryForSelfJoin(Builder $query, Builder $parent, $columns = ['*']) { $query->select($columns); $query->from($this->related->getTable() . ' as ' . ($hash = $this->getRelationCountHash())); $this->related->setTable($hash); $this->setJoin($query); return parent::getRelationQuery($query, $parent, $columns); }
/** * Adds scope to get a list of translated attributes, using the current locale. * * Example usage: Country::listsTranslations('name')->get()->toArray() * Will return an array with items: * [ * 'id' => '1', // The id of country * 'name' => 'Griechenland' // The translated name * ] * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $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(), $this->locale()); 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(), $this->locale()); }); }); } }
/** * Add the constraints for a relationship count query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent) { $query->select(new \Illuminate\Database\Query\Expression('count(*)')); $query->from($this->table . ' as ' . ($hash = $this->getRelationCountHash())); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($hash . '.' . $this->foreignKey, '=', new \Illuminate\Database\Query\Expression($key)); }
/** * Add the constraints for a relationship count query on the same table. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $tablePrefix = $this->query->getQuery()->getConnection()->getTablePrefix(); $query->from($this->table . ' as ' . $tablePrefix . ($hash = $this->getRelationCountHash())); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($hash . '.' . $this->foreignKey, '=', new Expression($key)); }
/** * Add the constraints for a relationship count query. * * @param \Illuminate\Database\Eloquent\Builder $query * @param \Illuminate\Database\Eloquent\Builder $parent * @return \Illuminate\Database\Eloquent\Builder */ public function getRelationCountQuery(Builder $query, Builder $parent) { $query->select(new Expression('count(*)')); $key = $this->wrap($this->getQualifiedParentKeyName()); return $query->where($this->getHasCompareKey(), '=', new Expression($key)); }