PHP Builder::leftJoin方法代码示例

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