/**
* @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));
}
