/** * Handle the filter. * * @param Builder $query * @param SearchFilterInterface $filter */ public function handle(Builder $query, TableBuilder $builder, SearchFilterInterface $filter) { $stream = $filter->getStream(); $model = $builder->getTableModel(); /** * If the model is translatable then * join it's translations so they * are filterable too. * * @var EloquentQueryBuilder $query */ if ($model->getTranslationModelName() && !$query->hasJoin($model->getTranslationTableName())) { $query->leftJoin($model->getTranslationTableName(), $model->getTableName() . '.id', '=', $model->getTranslationTableName() . '.' . $model->getRelationKey()); } $query->where(function (Builder $query) use($filter, $stream) { foreach ($filter->getColumns() as $column) { $query->orWhere($column, 'LIKE', "%{$filter->getValue()}%"); } foreach ($filter->getFields() as $field) { $filter->setField($field); $fieldType = $stream->getFieldType($field); $fieldTypeQuery = $fieldType->getQuery(); $fieldTypeQuery->setConstraint('or'); $this->container->call([$fieldTypeQuery, 'filter'], compact('query', 'filter', 'builder')); } }); }
public function build(Builder $query) { if ($this->person === null) { return $query->where(Page::ATTR_ENABLE_ACL, false); } return $query->leftJoin('page_acl', 'pages.id', '=', 'page_acl.page_id')->leftJoin('group_person', 'page_acl.group_id', '=', 'group_person.group_id')->where(function (Builder $where) { $where->where(Page::ATTR_CREATED_BY, $this->person->getId())->orWhereNull('page_acl.group_id')->orWhere('group_person.person_id', $this->person->getId()); })->groupBy('pages.id'); }
/** * Overriding newQuery() to the custom PostBuilder with some intereting methods * * @param bool $excludeDeleted * @return Corcel\PostBuilder */ public function newQuery($excludeDeleted = true) { $builder = new Builder($this->newBaseQueryBuilder()); $builder->setModel($this); $builder->leftJoin('wp_term_taxonomy', 'wp_terms.term_id', '=', 'wp_term_taxonomy.term_id')->where('wp_term_taxonomy.taxonomy', '=', 'category')->select('wp_terms.*'); if ($excludeDeleted and $this->softDelete) { $builder->whereNull($this->getQualifiedDeletedAtColumn()); } return $builder; }
public function build(Builder $query) { foreach ($this->tags as $i => $tag) { $alias = "pt_without-{$i}"; $query->leftJoin("pages_tags as {$alias}", function ($q) use($tag, $alias) { $q->on('pages.id', '=', "{$alias}.page_id")->on("{$alias}.tag_id", '=', DB::raw($tag->getId())); })->whereNull("{$alias}.page_id"); } return $query; }
public function apply(Builder $builder) { $select = ['Simulation.*', 'Power_Generator.Modality_Id']; $builder->with(['Combination.NumericalModel', 'Combination.Protocol', 'Combination.PowerGenerator', 'Combination.PowerGenerator.Modality'])->leftJoin('Power_Generator', 'Power_Generator.Id', '=', 'Combination.Power_Generator_Id')->leftJoin('Combination', 'Combination.Combination_Id', '=', 'Simulation.Combination_Id'); if (Config::get('gosmart.integrated_patient_database')) { $builder->leftJoin('ItemSet as SimulationItem', 'SimulationItem.Id', '=', 'Simulation.Id')->leftJoin('ItemSet as PatientItem', 'PatientItem.Id', '=', 'Simulation.Patient_Id')->leftJoin('ItemSet_Patient', 'ItemSet_Patient.Id', '=', 'Simulation.Patient_Id')->leftJoin('ItemSet_VtkFile as SimulatedLesionSurface', 'SimulatedLesionSurface.Simulation_Id', '=', 'Simulation.Id')->leftJoin('ItemSet_VtuFile as SimulatedLesionVolume', 'SimulatedLesionVolume.Simulation_Id', '=', 'Simulation.Id')->leftJoin('ItemSet_Segmentation', function ($leftJoin) { $leftJoin->on('ItemSet_Segmentation.Patient_Id', '=', 'Simulation.Patient_Id'); $leftJoin->on('ItemSet_Segmentation.State', '=', DB::raw('3')); $leftJoin->on('ItemSet_Segmentation.SegmentationType', '=', DB::raw(SegmentationTypeEnum::Lesion)); })->leftJoin('ItemSet_VtkFile as LesionFile', 'LesionFile.Segmentation_Id', '=', 'ItemSet_Segmentation.Id')->leftJoin('AspNetUsers as Clinician', 'Clinician.Id', '=', 'ItemSet_Patient.AspNetUsersId'); $select = array_merge($select, ['SimulationItem.CreationDate as creationDate', 'LesionFile.Id as SegmentedLesionId', 'Clinician.Id as ClinicianId', 'Clinician.UserName as ClinicianUserName', 'ItemSet_Patient.Alias as PatientAlias', 'ItemSet_Patient.Description as PatientDescription', 'SimulatedLesionSurface.Id as SimulatedLesionSurfaceId', 'SimulatedLesionVolume.Id as SimulatedLesionVolumeId']); } $builder->select($select); }
/** * 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(); $translations = $stream->getEntryTranslationsTableName(); if ($assignment->isTranslatable()) { if ($query instanceof EloquentQueryBuilder && !$query->hasJoin($translations)) { $query->leftJoin($stream->getEntryTranslationsTableName(), $stream->getEntryTableName() . '.id', '=', $stream->getEntryTranslationsTableName() . '.entry_id'); } $query->addSelect($translations . '.locale'); $query->addSelect($translations . '.' . $column); $query->{$this->where()}(function (Builder $query) use($stream, $filter, $column) { $query->where($stream->getEntryTranslationsTableName() . '.locale', config('app.locale')); $query->where($stream->getEntryTranslationsTableName() . '.' . $column, 'LIKE', "%" . $filter->getValue() . "%"); }); } else { $query->{$this->where()}(function (Builder $query) use($stream, $filter, $column) { $query->where($stream->getEntryTableName() . '.' . $column, 'LIKE', "%" . $filter->getValue() . "%"); }); } }
/** * 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; }
/** * @codeCoverageIgnore * * @param EloquentBuilder $query * @param array $types */ public function scopeTransactionTypes(EloquentBuilder $query, array $types) { if (is_null($this->joinedTransactionTypes)) { $query->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id'); $this->joinedTransactionTypes = true; } $query->whereIn('transaction_types.type', $types); }
/** * Adds the sql joins to the query. * * @param \Illuminate\Database\Eloquent\Builder $query */ protected function makeJoins(Builder $query) { foreach ($this->getJoins() as $table => $keys) { $query->leftJoin($table, function ($join) use($keys) { $join->on($keys[0], '=', $keys[1]); if (array_key_exists(2, $keys) && array_key_exists(3, $keys)) { $join->where($keys[2], '=', $keys[3]); } }); } }
/** * @codeCoverageIgnore * * @param EloquentBuilder $query * @param string $name * @param string $value */ public function scopeHasMetaValue(EloquentBuilder $query, $name, $value) { $joinName = str_replace('.', '_', $name); $query->leftJoin('account_meta as ' . $joinName, function (JoinClause $join) use($joinName, $name) { $join->on($joinName . '.account_id', '=', 'accounts.id')->where($joinName . '.name', '=', $name); }); $query->where($joinName . '.data', json_encode($value)); }
/** * Apply join query. * * @param \Illuminate\Database\Eloquent\Builder $query * @param string $foreignTable * @param string $foreignKey * @param bool $joinToParent * @return void */ public function join($query, $foreignTable = null, $foreignKey = null) { $foreignTable = $foreignTable ?: $query->getModel()->getTable(); $foreignKey = $foreignKey ?: $this->foreignKey; $query->leftJoin($this->tableToJoin(), "{$this->tableAlias}.{$this->primaryKey}", '=', "{$foreignTable}.{$foreignKey}"); }
/** * Adds the sql joins to the query. * * @param \Illuminate\Database\Eloquent\Builder $query */ protected function makeJoins(Builder $query) { foreach ($this->getJoins() as $table => $keys) { $query->leftJoin($table, $keys[0], '=', $keys[1]); } }
/** * @param Builder $query */ protected function setRelationQueryConstraints(Builder $query) { $one = $this->getRelated()->getQualifiedKeyName(); $prev = $this->getForeignKey($this->getRelated()); $alias = null; $lastIndex = count($this->models); foreach ($this->models as $index => $model) { if ($lastIndex === $index) { $prev = $this->prefix . $prev; // TODO: Check if this line is really necessary. Its not covered by any of the tests. } 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)); }
/** * @param Builder $query * @param $value */ public function scopeWithReportValue(Builder $query, $value) { // only join if query has no join yet $joined = false; /** @var JoinClause $join */ if (!is_null($query->getQuery()->joins)) { foreach ($query->getQuery()->joins as $join) { if ($join->table == 'reports') { $joined = true; } } } if (!$joined) { $query->leftJoin('reports', DB::Raw('DATE_FORMAT(log_entries.time,"%y%m%d%H%i")'), '=', DB::Raw('DATE_FORMAT(reports.time, "%y%m%d%H%i")')); } $joinName = str_replace('.', '_', $value); $query->leftJoin('report_values as ' . $joinName, function (JoinClause $join) use($value, $joinName) { $join->on('reports.id', '=', $joinName . '.report_id')->where($joinName . '.name', '=', $value); }); }