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