/** * @param string $table table name * @param string $key $table.key * @param string|bool $fkey $this->table.key * @return $this */ public function innerJoinOn($table, $key, $fkey = false) { $fkey = $fkey ?: $key; $key = starts_with($key, $table . '.') ? $key : $table . '.' . $key; $fkey = starts_with($key, $this->table . '.') ? $fkey : $this->table . '.' . $fkey; $this->operator = $this->operator->join($table, $key, '=', $fkey); return $this; }
/** * @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); }
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); }
/** * @param \Illuminate\Database\Query\Builder $query * @param string $table join the table * @param string $one joins first parameter * @param string|array|null $operatorOrCollumns operator condition or collums list * @param string $two joins two parameter * @param string $type join type (left, right, '', etc) * @param bool|false $where custom where condition * @param array $collumns if you will not pass collumns, it will retreive the collumn listing. If you pass null * it will not get any data from the model. * * @return \Illuminate\Database\Query\Builder */ public function scopeJoinWithSelect($query, $table, $one, $operatorOrCollumns, $two, $type = "left", $where = false, $collumns = array()) { // if the operator collumns are in if (is_array($operatorOrCollumns) || is_null($operatorOrCollumns)) { $collumns = $operatorOrCollumns; $operatorOrCollumns = "="; } if (!is_null($collumns)) { // if there is no specific collumns, lets get all if (empty($collumns)) { $collumns = \Schema::getColumnListing($table); } // build the table values prefixed by the table to ensure unique values foreach ($collumns as $related_column) { $query->addSelect(new Expression("`{$table}`.`{$related_column}` AS `{$table}.{$related_column}`")); } } return $query->join($table, $one, $operatorOrCollumns, $two, $type, $where); }
/** * Add a join clause to the query. * * @param string $table * @param string $one * @param string $operator * @param string $two * @param string $type * @param bool $where * @return $this * @static */ public static function join($table, $one, $operator = null, $two = null, $type = 'inner', $where = false) { return \Illuminate\Database\Query\Builder::join($table, $one, $operator, $two, $type, $where); }
/** * Add joins to query builder * @param \Illuminate\Database\Query\Builder $query object is by reference */ protected function addJoinQuery($query) { if (isset($this->join)) { foreach ($this->join as $join) { $query->join($join['table'], $join['one'], $join['operator'], $join['two']); } } }
/** * Parse the remaining filter params * * @param array $filterParams * @return void */ protected function parseFilter($filterParams) { $supportedPostfixes = ['st' => '<', 'gt' => '>', 'min' => '>=', 'max' => '<=', 'lk' => 'LIKE', 'not-lk' => 'NOT LIKE', 'in' => 'IN', 'not-in' => 'NOT IN', 'not' => '!=']; $supportedPrefixesStr = implode('|', $supportedPostfixes); $supportedPostfixesStr = implode('|', array_keys($supportedPostfixes)); foreach ($filterParams as $filterParamKey => $filterParamValue) { $keyMatches = []; //Matches every parameter with an optional prefix and/or postfix //e.g. not-title-lk, title-lk, not-title, title $keyRegex = '/^(?:(' . $supportedPrefixesStr . ')-)?(.*?)(?:-(' . $supportedPostfixesStr . ')|$)/'; preg_match($keyRegex, $filterParamKey, $keyMatches); if (!isset($keyMatches[3])) { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NULL'; } else { $comparator = '='; } } else { if (strtolower(trim($filterParamValue)) == 'null') { $comparator = 'NOT NULL'; } else { $comparator = $supportedPostfixes[$keyMatches[3]]; } } $column = $keyMatches[2]; //resolve a relation->relation_column if we are using an eloquent builder. if (strpos($column, '->') !== false && $this->isEloquentBuilder) { $model = $this->builder->getModel(); //get the relation and the column in the relation $relationComponents = explode('->', $column); if (!method_exists($model, $relationComponents[0])) { throw new ApiHandlerException('UnknownResourceRelation', ['relation' => $relationComponents[0]]); } $relation = call_user_func([$model, $relationComponents[0]]); $relationColumn = $relationComponents[1]; $relationType = $this->getRelationType($relation); if ($relationType === 'BelongsTo') { $firstKey = $relation->getQualifiedForeignKey(); $secondKey = $relation->getQualifiedOtherKeyName(); } else { if ($relationType === 'HasMany' || $relationType === 'HasOne') { $firstKey = $relation->getQualifiedParentKeyName(); $secondKey = $relation->getForeignKey(); } else { if ($relationType === 'BelongsToMany') { $firstKey = $relation->getQualifiedParentKeyName(); $secondKey = $relation->getRelated()->getQualifiedKeyName(); } } } //get the table to join to $joinTable = $relation->getRelated()->getTable(); //do the join $this->query->join($joinTable, $firstKey, '=', $secondKey); //modify the $column name and continue parsing. $column = $joinTable . '.' . $relationColumn; $this->query->addSelect($model->getTable() . '.*'); } //should we be using an eloquent builder, append the table name to every column to differentiate from joined columns. if ($this->isEloquentBuilder) { if (strpos($column, '.') === false) { $column = $this->builder->getModel()->getTable() . '.' . $column; } } if ($comparator == 'IN') { $values = explode(',', $filterParamValue); $this->query->whereIn($column, $values); } else { if ($comparator == 'NOT IN') { $values = explode(',', $filterParamValue); $this->query->whereNotIn($column, $values); } else { $values = explode('|', $filterParamValue); if (count($values) > 1) { $this->query->where(function ($query) use($column, $comparator, $values) { foreach ($values as $value) { if ($comparator == 'LIKE' || $comparator == 'NOT LIKE') { $value = preg_replace('/(^\\*|\\*$)/', '%', $value); } //Link the filters with AND of there is a "not" and with OR if there's none if ($comparator == '!=' || $comparator == 'NOT LIKE') { $query->where($column, $comparator, $value); } else { $query->orWhere($column, $comparator, $value); } } }); } else { $value = $values[0]; if ($comparator == 'LIKE' || $comparator == 'NOT LIKE') { $value = preg_replace('/(^\\*|\\*$)/', '%', $value); } if ($comparator == 'NULL' || $comparator == 'NOT NULL') { $this->query->whereNull($column, 'and', $comparator == 'NOT NULL'); } else { $this->query->where($column, $comparator, $value); } } } } } }
/** * Is used to join tables to main query using config file * @param \Illuminate\Database\Query\Builder $query - Query Builder object - main query * @param $join_table_name - table we need to join * @param $current_table - table we join to * @param $type - inner, left, right * @return Illuminate\Database\Query\Builder $query - Query Builder object */ private static function joinTableToQuery($query, $join_table_name, $current_table, $type = 'inner') { $config_joins = Config::get('refinement.joins'); /* first we need to find join statement in configuration array */ $join_statement = array(); $join_statement_keys = array("{$current_table}|{$join_table_name}", "{$join_table_name}|{$current_table}"); foreach ($join_statement_keys as $join_statement_key) { if (empty($config_joins[$join_statement_key])) { continue; } $join_statement = $config_joins[$join_statement_key]; } /* if there is no record in config array for this table, skip it */ if (empty($join_statement)) { return $query; } return $query->join($join_table_name, $join_statement['left'], $join_statement['operand'], $join_statement['right'], $type); }
/** * @param \Illuminate\Database\Query\Builder|static $query * * @return \Illuminate\Database\Query\Builder|static */ public function scopeAllowedEbook($query) { return $query->join('ebook_reader', "{$this->table}.user_id", '=', 'ebook_reader.reader_id')->join('ebooks', "ebooks.id", '=', 'ebook_reader.ebook_id')->addSelect(['ebook_reader.expires_at', 'ebook_reader.ebook_id', 'ebooks.title as ebook_title'])->orderBy('ebook_reader.created_at'); }