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