/**
* Handle the command.
*
* @param RoleRepositoryInterface $roles
*/
public function handle(RoleRepositoryInterface $roles)
{
if (!$this->query->getQuery()->joins && ($permission = array_get($this->fieldType->getConfig(), 'permission'))) {
$accessible = $roles->findByPermission($permission);
if (!$accessible->isEmpty()) {
$this->query->join('users_users_roles', 'users_users_roles.entry_id', '=', 'users_users.id')->whereIn('users_users_roles.related_id', $accessible->lists('id'));
}
}
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if (isset($model->required) && $model->required) {
$builder->join('tmp_documents', function ($join) {
$join->on('tmp_documents.id', '=', 'persons_documents.document_id')->where('is_required', '=', true)->wherenull('persons_documents.deleted_at');
});
} else {
$builder->join('tmp_documents', function ($join) {
$join->on('tmp_documents.id', '=', 'persons_documents.document_id')->where('is_required', '=', false)->wherenull('persons_documents.deleted_at');
});
}
}
/**
* 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.*']);
}
public function build(Builder $query)
{
$tagIds = array_map(function (TagInterface $tag) {
return $tag->getId();
}, $this->tags);
return $query->join('pages_tags', 'pages.id', '=', 'pages_tags.page_id')->whereIn('pages_tags.tag_id', $tagIds)->groupBy('pages.id')->having(DB::raw('count(distinct pages_tags.tag_id)'), '=', count($tagIds));
}
public function build(Builder $query)
{
$page = $this->page;
return $query->join('pages_tags', 'tags.id', '=', 'pages_tags.tag_id')->join('pages', 'pages_tags.page_id', '=', 'pages.id')->where(function ($query) use($page) {
$query->where('pages.id', '=', $page->getId())->orWhere('pages.parent_id', '=', $page->getId());
})->groupBy('tags.id')->orderBy('tags.name', 'asc');
}
/**
* Fired just before querying
* for table entries.
*
* @param Builder $query
*/
public function onQuerying(Builder $query)
{
$uploaded = $this->getUploaded();
if ($fieldType = $this->getFieldType()) {
/*
* If we have the entry available then
* we can determine saved sort order.
*/
$entry = $fieldType->getEntry();
$table = $fieldType->getPivotTableName();
if ($entry->getId() && !$uploaded) {
$query->join($table, $table . '.file_id', '=', 'files_files.id');
$query->where($table . '.entry_id', $entry->getId());
$query->orderBy($table . '.sort_order', 'ASC');
} else {
$query->whereIn('id', $uploaded ?: [0]);
}
} else {
/*
* If all we have is ID then just use that.
* The JS / UI will be handling the sort
* order at this time.
*/
$query->whereIn('id', $uploaded ?: [0]);
}
}
/**
* Get the siblings of the specified entry ID
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param int|array $entryId
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeSiblings(Builder $query, $entryId)
{
$entryId = is_array($entryId) ? $entryId : array($entryId);
$connection = $query->getQuery()->getConnection();
$tablePrefix = $connection->getTablePrefix();
return $query->join('relationships', 'relationships.child_id', '=', 'channel_titles.entry_id')->join($connection->raw("`{$tablePrefix}relationships` AS `{$tablePrefix}relationships_2`"), 'relationships_2.parent_id', '=', 'relationships.parent_id')->addSelect('*')->addSelect('relationships_2.child_id AS sibling_id')->whereIn('relationships_2.child_id', $entryId)->orderBy('relationships.order', 'asc')->groupBy('relationships_2.child_id')->groupBy('relationships.field_id')->groupBy('channel_titles.entry_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.*']);
}
/**
* Apply the scope to a given Eloquent query builder.
* @param Builder $builder
* @param Model $model
*/
public function apply(Builder $builder, Model $model)
{
$builder->join('uris', function ($join) use($model) {
$join->where('uris.owner_type', '=', get_class($model));
$join->on('uris.owner_id', '=', $model->getTable() . '.' . $model->getKeyName());
})->select([$model->getTable() . '.*', 'uris.uri']);
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->join($model->getVersionTable(), function ($join) use($model) {
$join->on($model->getQualifiedKeyName(), '=', $model->getQualifiedVersionKeyName());
$join->on($model->getQualifiedVersionColumn(), '=', $model->getQualifiedLatestVersionColumn());
});
$this->extend($builder);
}
public function build(Builder $query)
{
foreach ($this->tags as $i => $tag) {
$alias = "tag-{$i}";
$query->join("pages_tags as {$alias}", 'pages.id', '=', "{$alias}.page_id")->where("{$alias}.tag_id", '=', $tag->getId());
}
return $query;
}
/**
* Handle the command.
*
* @param RoleRepositoryInterface $roles
*/
public function handle(RoleRepositoryInterface $roles)
{
if ($role = array_get($this->fieldType->getConfig(), 'role')) {
if (is_numeric($role)) {
$role = $roles->find($role);
}
if (is_string($role)) {
$role = $roles->findBySlug($role);
}
if ($role) {
// The role exists so join and limit results to that role's ID.
$this->query->join('users_users_roles', 'users_users_roles.entry_id', '=', 'users_users.id')->where('users_users_roles.related_id', $role->getId());
} else {
// The role doesn't exist so don't return anything.
$this->query->join('users_users_roles', 'users_users_roles.entry_id', '=', 'users_users.id')->where('users_users_roles.related_id', false);
}
}
}
/**
* @param string $table
* @param array $ons
* @param array $conditions
* @param string $joinType
*
* @throws \InvalidArgumentException
*/
private function generateJoin($table, $ons, $conditions = array(), $joinType = '')
{
$this->query->join($table, function (JoinClause $q) use($conditions, $ons) {
foreach ($ons as $on) {
$q->on($on[0], array_key_exists(2, $on) ? $on[2] : '=', $on[1]);
}
$this->generateJoinConstraints($q, $conditions);
}, null, null, $joinType === '' ? $this->joinType : $joinType);
}
public function build(Builder $query)
{
$query->join('assets_tags', 'assets_tags.asset_id', '=', 'assets.id');
if (is_array($this->tags)) {
$query->whereIn('assets_tags.tag', $this->tags)->groupBy('tag')->having(DB::raw('count(distinct tag)'), '=', count($this->tags));
} else {
$query->where('assets_tags.tag', '=', $this->tags);
}
return $query;
}
public function modifyQuery(Builder $query)
{
$query->join('users', 'users.id', '=', 'team_users.user_id');
if ($this->get('team_id')) {
$query->where('team_id', $this->get('team_id'));
}
if ($this->get('user_id')) {
$query->where('user_id', $this->get('user_id'));
}
return $query;
}
protected function joinManyToManyRelation(Relations\BelongsToMany $relation, $type)
{
$pivotTable = $relation->getTable();
// $relation->getQualifiedParentKeyName() is protected
$parentKey = $relation->getParent()->getQualifiedKeyName();
$localKey = $relation->getOtherKey();
$this->query->join($pivotTable, $localKey, '=', $parentKey, $type);
$related = $relation->getRelated();
$foreignKey = $relation->getForeignKey();
$relatedTable = $related->getTable();
$relatedKey = $related->getQualifiedKeyName();
$this->query->join($relatedTable, $foreignKey, '=', $relatedKey, $type);
}
/**
* Filter a query by the value of a
* field using this field type.
*
* @param Builder $query
* @param FilterInterface $filter
*/
public function filter(Builder $query, FilterInterface $filter)
{
$stream = $filter->getStream();
$assignment = $stream->getAssignment($filter->getField());
$column = $this->fieldType->getColumnName();
$table = $stream->getEntryTableName();
$translations = $stream->getEntryTranslationsTableName();
$modifier = $this->fieldType->getModifier();
if ($assignment->isTranslatable()) {
$query->join($translations, $translations . '.entry_id', '=', $table . '.id')->where($translations . '.' . $column, $modifier->modify($filter->getValue()))->where('locale', config('app.locale'));
} else {
$query->where($column, $modifier->modify($filter->getValue()));
}
}
/**
* @param Builder $builder
* @param Collection $tags
* @return Builder
*/
public function scopeTaggedWith(Builder $builder, Collection $tags)
{
/** @var MorphToMany $relation */
$relation = $this->tags();
$key = $this->getTable() . '.' . $this->getKeyName();
if ($tags->count()) {
$builder->join($relation->getTable(), function ($join) use($relation, $key, $tags) {
$join->on($relation->getForeignKey(), '=', $key);
$join->where($relation->getMorphType(), '=', $relation->getMorphClass());
$join->whereIn($relation->getOtherKey(), $tags->keys()->toArray());
});
} else {
$builder->whereNull($this->getTable());
}
return $builder;
}
public function apply(Query $query, BaseRepository $repository)
{
$meta = false;
if (isset($this->filter['meta'])) {
$meta = $this->filter['meta'];
unset($this->filter['meta']);
}
if (!empty($this->filter)) {
$query->where($this->filter);
}
if (!empty($meta)) {
$table = $query->getModel()->getTable();
$query->join('meta', $table . '.id', '=', 'meta.object_id');
$metaQuery = $query->getQuery()->newQuery();
foreach ($meta as $k => $v) {
$metaQuery->orWhere(['meta.key' => $k, 'meta.value' => $v]);
}
$query->addNestedWhereQuery($metaQuery);
$query->groupBy($table . '.id');
$results = $query->get();
}
}
/**
* @param Builder $query
* @param $attribute
* @param $direction
* @return mixed
*/
protected function orderQueryBySourceAttribute(Builder $query, $attribute, $direction)
{
$table = $this->_isTranslationAttribute($attribute) ? $this->sourcesTable : source_table_name($this->getNodeTypeName());
$key = $this->getTable() . '.' . $this->getKeyName();
return $query->join($table . ' as t', 't.node_id', '=', $key)->select('t.id as source_id', 'nodes.*')->groupBy($key)->orderBy('t.' . $attribute, $direction);
}
/**
* @param \Illuminate\Database\Eloquent\Builder $query
* @link https://laravel.com/docs/5.2/eloquent#query-scopes
*
* @return \Illuminate\Database\Eloquent\Builder $query
*/
public static function scopeUserGroup(Builder $query)
{
return $query->join('user_groups', 'users.id', '=', 'user_groups.user_id');
}
/**
* 扩展查询，获取不及格的前修课程
* @author FuRongxin
* @date 2016-02-23
* @version 2.0
* @param \Illuminate\Database\Eloquent\Builder $query 查询对象
* @param string $course 8位课程号
* @param object $user 用户对象
* @return \Illuminate\Database\Eloquent\Builder 查询对象
*/
public function scopeFailed($query, $course, $user)
{
return $query->join('cj_zxscj', 'cj_zxscj.kch', '=', 'jx_kc_qxgx.kch')->where('cj_zxscj.xh', '=', $user->xh)->where('cj_zxscj.xf', '<=', 0)->where('kch2', '=', $course)->where('gx', '=', '>');
}
public function execute(Builder $query)
{
return $query->join('asset_site', 'asset.id', '=', 'asset_site.asset_id')->where('asset_site.site_id', '=', $this->site->getId());
}
private function joinRelatedModelIfExists(Builder $query, $key)
{
$model = $query->getModel();
// relationship exists, join to make special sort
if (method_exists($model, $key)) {
$relation = $model->{$key}();
$type = 'inner';
if ($relation instanceof BelongsTo) {
$query->join($relation->getRelated()->getTable(), $model->getTable() . '.' . $relation->getForeignKey(), '=', $relation->getRelated()->getTable() . '.' . $relation->getOtherKey(), $type);
} elseif ($relation instanceof BelongsToMany) {
$query->join($relation->getTable(), $relation->getQualifiedParentKeyName(), '=', $relation->getForeignKey(), $type);
$query->join($relation->getRelated()->getTable(), $relation->getRelated()->getTable() . '.' . $relation->getRelated()->getKeyName(), '=', $relation->getOtherKey(), $type);
} else {
$query->join($relation->getRelated()->getTable(), $relation->getQualifiedParentKeyName(), '=', $relation->getForeignKey(), $type);
}
$table = $model->getTable();
$query->select(sprintf('%s.*', $table));
}
}
/**
* @param Builder $query
* @param $attribute
* @param $direction
* @return mixed
*/
protected function orderByTranslationAttribute(Builder $query, $attribute, $direction)
{
$key = $this->getTable() . '.' . $this->getKeyName();
return $query->join($this->getTranslationsTable() . ' as t', 't.tag_id', '=', $key)->select('t.id as translation_id', 'tags.*')->groupBy($key)->orderBy('t.' . $attribute, $direction);
}
/**
* @param Builder $query
*
* @return Builder
*/
public function execute(Builder $query)
{
return $query->join('pages_relations', 'pages.id', '=', 'pages_relations.page_id')->where('pages_relations.related_page_id', '=', $this->page->getId());
}
/**
* Scopes class by item sku.
* Optionally, scopes by status.
*
* @param \Illuminate\Database\Eloquent\Builder $query Query.
* @param mixed $sku Item SKU.
*
* @return this
*/
public function scopeWhereSKU($query, $sku)
{
return $query->join(config('shop.item_table'), config('shop.item_table') . '.order_id', '=', $this->table . '.id')->where(config('shop.item_table') . '.sku', $sku);
}
/**
* Only returns clippings for specifically named files.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $name
* @return Builder
*/
public function scopeNamed(Builder $query, $name)
{
return $query->join('phperclip_file_meta', 'phperclip_file_meta.id', '=', 'phperclip_clipping.file_meta_id')->where('phperclip_file_meta.name', $name);
}
/**
* 扩展查询：用于获取可选课程列表
* @author FuRongxin
* @date 2016-03-01
* @version 2.0
* @param \Illuminate\Database\Eloquent\Builder $query 查询对象
* @param string $campus 校区号
* @return \Illuminate\Database\Eloquent\Builder 查询对象
*/
public function scopeSelectable($query, $campus)
{
$campus = 'unknown' == $campus ? '' : $campus;
return $query->join('jx_jxjh', function ($join) {
$join->on('pk_kczy.zy', '=', 'jx_jxjh.zy')->on('pk_kczy.nj', '=', 'jx_jxjh.nj')->on('pk_kczy.zsjj', '=', 'jx_jxjh.zsjj');
})->join('zd_khfs', 'jx_jxjh.kh', '=', 'zd_khfs.dm')->join('jx_kc', 'jx_jxjh.kch', '=', 'jx_kc.kch')->join('pk_kb', function ($join) {
$join->on('pk_kczy.nd', '=', 'pk_kb.nd')->on('pk_kczy.xq', '=', 'pk_kb.xq')->on('pk_kczy.kcxh', '=', 'pk_kb.kcxh');
})->join('pk_js', 'pk_kb.jsgh', '=', 'pk_js.jsgh')->leftJoin('xk_tj', function ($join) {
$join->on('pk_kczy.kcxh', '=', 'xk_tj.kcxh')->on('pk_kczy.zy', '=', 'xk_tj.zy');
})->where('pk_kb.xqh', '=', $campus)->whereRaw('t_jx_jxjh.kch = substring(t_pk_kczy.kcxh, 3, 8)')->where('pk_kczy.nd', '=', session('year'))->where('pk_kczy.xq', '=', session('term'))->where('pk_kczy.zsjj', '=', session('season'))->groupBy('pk_kczy.kcxh', 'jx_jxjh.kch', 'jx_jxjh.zxf', 'jx_jxjh.kh', 'jx_kc.kcmc', 'pk_kczy.rs', 'pk_kb.xqh', 'xk_tj.rs', 'zd_khfs.mc')->select('pk_kczy.kcxh', 'jx_jxjh.kch', 'jx_jxjh.zxf', 'jx_jxjh.kh', 'jx_kc.kcmc', 'pk_kczy.rs AS zrs', 'pk_kb.xqh', 'xk_tj.rs', 'zd_khfs.mc AS kh')->addSelect(DB::raw('array_to_string(array_agg(t_pk_kb.zc), \',\') AS zcs'))->addSelect(DB::raw('array_to_string(array_agg(t_pk_kb.ksz), \',\') AS kszs'))->addSelect(DB::raw('array_to_string(array_agg(t_pk_kb.jsz), \',\') AS jszs'))->addSelect(DB::raw('array_to_string(array_agg(t_pk_kb.ksj), \',\') AS ksjs'))->addSelect(DB::raw('array_to_string(array_agg(t_pk_kb.jsj), \',\') AS jsjs'))->addSelect(DB::raw('array_to_string(array_agg(t_pk_js.xm), \',\') AS jsxms'))->orderBy('pk_kczy.kcxh');
}
/**
* Inner join with the translation table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param null $locale
*
* @return this
*/
public static function scopeJoinTranslation(Builder $query, $locale = null)
{
$instance = new static();
$translationModelName = $instance->getTranslationModelName();
$translationModel = new $translationModelName();
if (is_null($locale)) {
$locale = $instance->locale();
}
return $query->join($translationModel->getTable() . ' as t', 't.' . $instance->getRelationKey(), '=', $instance->getTable() . '.' . $instance->getKeyName())->where($instance->getLocaleKey(), $locale);
}
