QueryBuilderEngine::globalSearch() - Code Metrics - Inspection of "Refactor using collection." - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d36753...bfa7ec )

by Arjay

created 2017-06-29 16:53 UTC

QueryBuilderEngine::globalSearch() A

↳ Parent: QueryBuilderEngine

Complexity

Conditions	3
Paths	1

Size

Total Lines	20
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	3
eloc	13
nc	1
nop	1
dl	0
loc	20
rs	9.4285
c	0
b	0
f	0

<?php

namespace Yajra\Datatables\Engines;

use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
use Illuminate\Database\Eloquent\Relations\MorphToMany;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Str;

/**
 * Class QueryBuilderEngine.
 *
 * @package Yajra\Datatables\Engines
 * @author  Arjay Angeles <[email protected]>
 */
class QueryBuilderEngine extends BaseEngine
{
    /**
     * Builder object.
     *
     * @var \Illuminate\Database\Query\Builder
     */
    protected $query;

    /**
     * Database connection used.
     *
     * @var \Illuminate\Database\Connection
     */
    protected $connection;

    /**
     * @param \Illuminate\Database\Query\Builder $builder
     */
    public function __construct(Builder $builder)
    {
        $this->query      = $builder;
        $this->request    = resolve('datatables.request');
        $this->config     = resolve('datatables.config');
        $this->columns    = $builder->columns;
        $this->connection = $builder->getConnection();
        if ($this->config->isDebugging()) {
            $this->connection->enableQueryLog();
        }
    }

    /**
     * Set auto filter off and run your own filter.
     * Overrides global search.
     *
     * @param callable $callback
     * @param bool     $globalSearch
     * @return $this
     */
    public function filter(callable $callback, $globalSearch = false)
    {
        $this->overrideGlobalSearch($callback, $this->query, $globalSearch);

        return $this;
    }

    /**
     * Perform global search for the given keyword.
     *
     * @param string $keyword
     */
    protected function globalSearch($keyword)
    {
        $this->query->where(function ($query) use ($keyword) {
            $query = $this->getBaseQueryBuilder($query);

            collect($this->request->searchableColumnIndex())->map(function ($index) {
                return $this->getColumnName($index);
            })->reject(function($column) {
                return $this->isBlacklisted($column) && !$this->hasCustomFilter($column);
            })->each(function ($column) use ($keyword, $query) {
                if ($this->hasCustomFilter($column)) {
                    $this->applyFilterColumn($query, $column, $keyword, 'or');
                } else {
                    $this->compileQuerySearch($query, $column, $keyword);
                }

                $this->isFilterApplied = true;
            });
        });
    }

    /**
     * Get the base query builder instance.
     *
     * @param mixed $instance
     * @return \Illuminate\Database\Query\Builder
     */
    protected function getBaseQueryBuilder($instance = null)
    {
        if (!$instance) {
            $instance = $this->query;
        }

        if ($instance instanceof EloquentBuilder) {
            return $instance->getQuery();
        }

        return $instance;
    }

    /**
     * Check if column has custom filter handler.
     *
     * @param  string $columnName
     * @return bool
     */
    public function hasCustomFilter($columnName)
    {
        return isset($this->columnDef['filter'][$columnName]);
    }

    /**
     * Apply filterColumn api search.
     *
     * @param mixed  $query
     * @param string $columnName
     * @param string $keyword
     * @param string $boolean
     */
    protected function applyFilterColumn($query, $columnName, $keyword, $boolean = 'and')
    {
        $callback = $this->columnDef['filter'][$columnName]['method'];
        $builder  = $query->newQuery();
        $callback($builder, $keyword);
        $query->addNestedWhereQuery($builder, $boolean);
    }

    /**
     * Compile query builder where clause depending on configurations.
     *
     * @param mixed  $query
     * @param string $column
     * @param string $keyword
     * @param string $relation
     */
    protected function compileQuerySearch($query, $column, $keyword, $relation = 'or')
    {
        $column = $this->addTablePrefix($query, $column);
        $column = $this->castColumn($column);
        $sql    = $column . ' LIKE ?';

        if ($this->config->isCaseInsensitive()) {
            $sql = 'LOWER(' . $column . ') LIKE ?';
        }

        $query->{$relation . 'WhereRaw'}($sql, [$this->prepareKeyword($keyword)]);
    }

    /**
     * Patch for fix about ambiguous field.
     * Ambiguous field error will appear when query use join table and search with keyword.
     *
     * @param mixed  $query
     * @param string $column
     * @return string
     */
    protected function addTablePrefix($query, $column)
    {
        if (strpos($column, '.') === false) {
            $q = $this->getBaseQueryBuilder($query);
            if (!$q->from instanceof Expression) {
                $column = $q->from . '.' . $column;
            }
        }

        return $this->wrap($column);
    }

    /**
     * Wrap column with DB grammar.
     *
     * @param string $column
     * @return string
     */
    protected function wrap($column)
    {
        return $this->connection->getQueryGrammar()->wrap($column);
    }

    /**
     * Wrap a column and cast based on database driver.
     *
     * @param  string $column
     * @return string
     */
    protected function castColumn($column)
    {
        switch ($this->connection->getDriverName()) {
            case 'pgsql':
                return 'CAST(' . $column . ' as TEXT)';
            case 'firebird':
                return 'CAST(' . $column . ' as VARCHAR(255))';
            default:
                return $column;
        }
    }

    /**
     * Prepare search keyword based on configurations.
     *
     * @param string $keyword
     * @return string
     */
    protected function prepareKeyword($keyword)
    {
        if ($this->config->isCaseInsensitive()) {
            $keyword = Str::lower($keyword);
        }

        if ($this->config->isWildcard()) {
            $keyword = $this->wildcardLikeString($keyword);
        }

        if ($this->config->isSmartSearch()) {
            $keyword = "%$keyword%";
        }

        return $keyword;
    }

    /**
     * Organizes works.
     *
     * @param bool $mDataSupport
     * @return \Illuminate\Http\JsonResponse
     * @throws \Exception
     */
    public function make($mDataSupport = false)
    {
        try {
            $this->totalRecords = $this->totalCount();

            if ($this->totalRecords) {
                $this->filterRecords();
                $this->ordering();
                $this->paginate();
            }

            $data = $this->transform($this->getProcessedData($mDataSupport));

            return $this->render($data);
        } catch (\Exception $exception) {
            return $this->errorResponse($exception);
        }
    }

    /**
     * Count total items.
     *
     * @return integer
     */
    public function totalCount()
    {
        return $this->totalRecords ? $this->totalRecords : $this->count();
    }

    /**
     * Counts current query.
     *
     * @return int
     */
    public function count()
    {
        $builder = $this->prepareCountQuery();
        $table   = $this->connection->raw('(' . $builder->toSql() . ') count_row_table');

        return $this->connection->table($table)
                                ->setBindings($builder->getBindings())
                                ->count();
    }

    /**
     * Prepare count query builder.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function prepareCountQuery()
    {
        $builder = clone $this->query;

        if ($this->isComplexQuery($builder)) {
            $row_count = $this->wrap('row_count');
            $builder->select($this->connection->raw("'1' as {$row_count}"));
        }

        return $builder;
    }

    /**
     * Check if builder query uses complex sql.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @return bool
     */
    protected function isComplexQuery($builder)
    {
        return !Str::contains(Str::lower($builder->toSql()), ['union', 'having', 'distinct', 'order by', 'group by']);
    }

    /**
     * Perform sorting of columns.
     *
     * @return void
     */
    public function ordering()
    {
        if ($this->orderCallback) {
            call_user_func($this->orderCallback, $this->getBaseQueryBuilder());

            return;
        }

        foreach ($this->request->orderableColumns() as $orderable) {
            $column = $this->getColumnName($orderable['column'], true);

            if ($this->isBlacklisted($column) && !$this->hasCustomOrder($column)) {
                continue;
            }

            if ($this->hasCustomOrder($column)) {
                $this->applyOrderColumn($column, $orderable);
            } else {
                $valid = 1;
                if (count(explode('.', $column)) > 1) {
                    $eagerLoads     = $this->getEagerLoads();
                    $parts          = explode('.', $column);
                    $relationColumn = array_pop($parts);
                    $relation       = implode('.', $parts);

                    if (in_array($relation, $eagerLoads)) {
                        // Loop for nested relations
                        // This code is check morph many or not.
                        // If one of nested relation is MorphToMany
                        // we will call joinEagerLoadedColumn.
                        $lastQuery     = $this->query;
                        $isMorphToMany = false;
                        foreach (explode('.', $relation) as $eachRelation) {
                            $relationship = $lastQuery->getRelation($eachRelation);
                            if (!($relationship instanceof MorphToMany)) {
                                $isMorphToMany = true;
                            }
                            $lastQuery = $relationship;
                        }
                        if ($isMorphToMany) {
                            $column = $this->joinEagerLoadedColumn($relation, $relationColumn);
                        } else {
                            $valid = 0;
                        }
                    }
                }

                if ($valid == 1) {
                    if ($this->nullsLast) {
                        $this->getBaseQueryBuilder()->orderByRaw($this->getNullsLastSql($column,
                            $orderable['direction']));
                    } else {
                        $this->getBaseQueryBuilder()->orderBy($column, $orderable['direction']);
                    }
                }
            }
        }
    }

    /**
     * Check if column has custom sort handler.
     *
     * @param string $column
     * @return bool
     */
    protected function hasCustomOrder($column)
    {
        return isset($this->columnDef['order'][$column]);
    }

    /**
     * Apply orderColumn custom query.
     *
     * @param string $column
     * @param array $orderable
     */
    protected function applyOrderColumn($column, $orderable): void
    {
        $sql      = $this->columnDef['order'][$column]['sql'];
        $sql      = str_replace('$1', $orderable['direction'], $sql);
        $bindings = $this->columnDef['order'][$column]['bindings'];
        $this->query->orderByRaw($sql, $bindings);
    }

    /**
     * Get eager loads keys if eloquent.
     *
     * @return array
     */
    protected function getEagerLoads()
    {
        if ($this->query instanceof EloquentBuilder) {
            return array_keys($this->query->getEagerLoads());
        }

        return [];
    }

    /**
     * Join eager loaded relation and get the related column name.
     *
     * @param string $relation
     * @param string $relationColumn
     * @return string
     */
    protected function joinEagerLoadedColumn($relation, $relationColumn)
    {
        $table     = '';
        $lastQuery = $this->query;
        foreach (explode('.', $relation) as $eachRelation) {
            $model = $lastQuery->getRelation($eachRelation);
            switch (true) {
                case $model instanceof BelongsToMany:
                    $pivot   = $model->getTable();
                    $pivotPK = $model->getExistenceCompareKey();
                    $pivotFK = $model->getQualifiedParentKeyName();
                    $this->performJoin($pivot, $pivotPK, $pivotFK);

                    $related = $model->getRelated();
                    $table   = $related->getTable();
                    $tablePK = $related->getForeignKey();
                    $foreign = $pivot . '.' . $tablePK;
                    $other   = $related->getQualifiedKeyName();

                    $lastQuery->addSelect($table . '.' . $relationColumn);
                    $this->performJoin($table, $foreign, $other);

                    break;

                case $model instanceof HasOneOrMany:
                    $table   = $model->getRelated()->getTable();
                    $foreign = $model->getQualifiedForeignKeyName();
                    $other   = $model->getQualifiedParentKeyName();
                    break;

                case $model instanceof BelongsTo:
                    $table   = $model->getRelated()->getTable();
                    $foreign = $model->getQualifiedForeignKey();
                    $other   = $model->getQualifiedOwnerKeyName();
                    break;

                default:
                    $table   = $model->getRelated()->getTable();
                    $foreign = $model->getQualifiedForeignKey();
                    $other   = $model->getQualifiedOtherKeyName();
            }
            $this->performJoin($table, $foreign, $other);
            $lastQuery = $model->getQuery();
        }

        return $table . '.' . $relationColumn;
    }

    /**
     * Perform join query.
     *
     * @param string $table
     * @param string $foreign
     * @param string $other
     */
    protected function performJoin($table, $foreign, $other)
    {
        $joins = [];
        foreach ((array) $this->getBaseQueryBuilder()->joins as $key => $join) {
            $joins[] = $join->table;
        }

        if (!in_array($table, $joins)) {
            $this->getBaseQueryBuilder()->leftJoin($table, $foreign, '=', $other);
        }
    }

    /**
     * Get NULLS LAST SQL.
     *
     * @param  string $column
     * @param  string $direction
     * @return string
     */
    protected function getNullsLastSql($column, $direction)
    {
        $sql = $this->config->get('datatables.nulls_last_sql', '%s %s NULLS LAST');

        return sprintf($sql, $column, $direction);
    }

    /**
     * Perform column search.
     *
     * @return void
     */
    public function columnSearch()
    {
        $columns = $this->request->columns();

        foreach ($columns as $index => $column) {
            if (!$this->request->isColumnSearchable($index)) {
                continue;
            }

            $column = $this->getColumnName($index);

            if ($this->hasCustomFilter($column)) {
                $keyword = $this->getColumnSearchKeyword($index, $raw = true);
                $this->applyFilterColumn($this->query, $column, $keyword);
            } else {
                if (count(explode('.', $column)) > 1) {
                    $eagerLoads     = $this->getEagerLoads();
                    $parts          = explode('.', $column);
                    $relationColumn = array_pop($parts);
                    $relation       = implode('.', $parts);
                    if (in_array($relation, $eagerLoads)) {
                        $column = $this->joinEagerLoadedColumn($relation, $relationColumn);
                    }
                }

                $keyword = $this->getColumnSearchKeyword($index);
                $this->compileColumnSearch($index, $column, $keyword);
            }

            $this->isFilterApplied = true;
        }
    }

    /**
     * Get column keyword to use for search.
     *
     * @param int  $i
     * @param bool $raw
     * @return string
     */
    protected function getColumnSearchKeyword($i, $raw = false)
    {
        $keyword = $this->request->columnKeyword($i);
        if ($raw || $this->request->isRegex($i)) {
            return $keyword;
        }

        return $this->setupKeyword($keyword);
    }

    /**
     * Compile queries for column search.
     *
     * @param int    $i
     * @param string $column
     * @param string $keyword
     */
    protected function compileColumnSearch($i, $column, $keyword)
    {
        if ($this->request->isRegex($i)) {
            $column = strstr($column, '(') ? $this->connection->raw($column) : $column;
            $this->regexColumnSearch($column, $keyword);
        } else {
            $this->compileQuerySearch($this->query, $column, $keyword, '');
        }
    }

    /**
     * Compile regex query column search.
     *
     * @param mixed  $column
     * @param string $keyword
     */
    protected function regexColumnSearch($column, $keyword)
    {
        switch ($this->connection->getDriverName()) {
            case 'oracle':
                $sql = !$this->config
                             ->isCaseInsensitive() ? 'REGEXP_LIKE( ' . $column . ' , ? )' : 'REGEXP_LIKE( LOWER(' . $column . ') , ?, \'i\' )';
                break;

            case 'pgsql':
                $sql = !$this->config->isCaseInsensitive() ? $column . ' ~ ?' : $column . ' ~* ? ';
                break;

            default:
                $sql     = !$this->config
                                 ->isCaseInsensitive() ? $column . ' REGEXP ?' : 'LOWER(' . $column . ') REGEXP ?';
                $keyword = Str::lower($keyword);
        }

        $this->query->whereRaw($sql, [$keyword]);
    }

    /**
     * Perform pagination.
     *
     * @return void
     */
    public function paging()
    {
        $this->query->skip($this->request->input('start'))
                    ->take((int) $this->request->input('length') > 0 ? $this->request->input('length') : 10);
    }

    /**
     * Get paginated results.
     *
     * @return \Illuminate\Support\Collection
     */
    public function results()
    {
        return $this->query->get();
    }

    /**
     * Add column in collection.
     *
     * @param string          $name
     * @param string|callable $content
     * @param bool|int        $order
     * @return \Yajra\Datatables\Engines\BaseEngine|\Yajra\Datatables\Engines\QueryBuilderEngine
     */
    public function addColumn($name, $content, $order = false)
    {
        $this->pushToBlacklist($name);

        return parent::addColumn($name, $content, $order);
    }

    /**
     * Get query builder instance.
     *
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder
     */
    public function getQuery()
    {
        return $this->query;
    }

    /**
     * Append debug parameters on output.
     *
     * @param  array $output
     * @return array
     */
    protected function showDebugger(array $output)
    {
        $output['queries'] = $this->connection->getQueryLog();
        $output['input']   = $this->request->all();

        return $output;
    }
}


1			<?php
2
3			namespace Yajra\Datatables\Engines;
4
5			use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
6			use Illuminate\Database\Eloquent\Relations\BelongsTo;
7			use Illuminate\Database\Eloquent\Relations\BelongsToMany;
8			use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
9			use Illuminate\Database\Eloquent\Relations\MorphToMany;
10			use Illuminate\Database\Query\Builder;
11			use Illuminate\Database\Query\Expression;
12			use Illuminate\Support\Str;
13
14			/**
15			* Class QueryBuilderEngine.
16			*
17			* @package Yajra\Datatables\Engines
18			* @author Arjay Angeles <[email protected]>
19			*/
20			class QueryBuilderEngine extends BaseEngine
21			{
22			/**
23			* Builder object.
24			*
25			* @var \Illuminate\Database\Query\Builder
26			*/
27			protected $query;
28
29			/**
30			* Database connection used.
31			*
32			* @var \Illuminate\Database\Connection
33			*/
34			protected $connection;
35
36			/**
37			* @param \Illuminate\Database\Query\Builder $builder
38			*/
39			public function __construct(Builder $builder)
40			{
41			$this->query = $builder;
42			$this->request = resolve('datatables.request');
43			$this->config = resolve('datatables.config');
44			$this->columns = $builder->columns;
45			$this->connection = $builder->getConnection();
46			if ($this->config->isDebugging()) {
47			$this->connection->enableQueryLog();
48			}
49			}
50
51			/**
52			* Set auto filter off and run your own filter.
53			* Overrides global search.
54			*
55			* @param callable $callback
56			* @param bool $globalSearch
57			* @return $this
58			*/
59			public function filter(callable $callback, $globalSearch = false)
60			{
61			$this->overrideGlobalSearch($callback, $this->query, $globalSearch);
62
63			return $this;
64			}
65
66			/**
67			* Perform global search for the given keyword.
68			*
69			* @param string $keyword
70			*/
71			protected function globalSearch($keyword)
72			{
73			$this->query->where(function ($query) use ($keyword) {
74			$query = $this->getBaseQueryBuilder($query);
75
76			collect($this->request->searchableColumnIndex())->map(function ($index) {
77			return $this->getColumnName($index);
78			})->reject(function($column) {
79			return $this->isBlacklisted($column) && !$this->hasCustomFilter($column);
80			})->each(function ($column) use ($keyword, $query) {
81			if ($this->hasCustomFilter($column)) {
82			$this->applyFilterColumn($query, $column, $keyword, 'or');
83			} else {
84			$this->compileQuerySearch($query, $column, $keyword);
85			}
86
87			$this->isFilterApplied = true;
88			});
89			});
90			}
91
92			/**
93			* Get the base query builder instance.
94			*
95			* @param mixed $instance
96			* @return \Illuminate\Database\Query\Builder
97			*/
98			protected function getBaseQueryBuilder($instance = null)
99			{
100			if (!$instance) {
101			$instance = $this->query;
102			}
103
104			if ($instance instanceof EloquentBuilder) {
105			return $instance->getQuery();
106			}
107
108			return $instance;
109			}
110
111			/**
112			* Check if column has custom filter handler.
113			*
114			* @param string $columnName
115			* @return bool
116			*/
117			public function hasCustomFilter($columnName)
118			{
119			return isset($this->columnDef['filter'][$columnName]);
120			}
121
122			/**
123			* Apply filterColumn api search.
124			*
125			* @param mixed $query
126			* @param string $columnName
127			* @param string $keyword
128			* @param string $boolean
129			*/
130			protected function applyFilterColumn($query, $columnName, $keyword, $boolean = 'and')
131			{
132			$callback = $this->columnDef['filter'][$columnName]['method'];
133			$builder = $query->newQuery();
134			$callback($builder, $keyword);
135			$query->addNestedWhereQuery($builder, $boolean);
136			}
137
138			/**
139			* Compile query builder where clause depending on configurations.
140			*
141			* @param mixed $query
142			* @param string $column
143			* @param string $keyword
144			* @param string $relation
145			*/
146			protected function compileQuerySearch($query, $column, $keyword, $relation = 'or')
147			{
148			$column = $this->addTablePrefix($query, $column);
149			$column = $this->castColumn($column);
150			$sql = $column . ' LIKE ?';
151
152			if ($this->config->isCaseInsensitive()) {
153			$sql = 'LOWER(' . $column . ') LIKE ?';
154			}
155
156			$query->{$relation . 'WhereRaw'}($sql, [$this->prepareKeyword($keyword)]);
157			}
158
159			/**
160			* Patch for fix about ambiguous field.
161			* Ambiguous field error will appear when query use join table and search with keyword.
162			*
163			* @param mixed $query
164			* @param string $column
165			* @return string
166			*/
167			protected function addTablePrefix($query, $column)
168			{
169			if (strpos($column, '.') === false) {
170			$q = $this->getBaseQueryBuilder($query);
171			if (!$q->from instanceof Expression) {
172			$column = $q->from . '.' . $column;
173			}
174			}
175
176			return $this->wrap($column);
177			}
178
179			/**
180			* Wrap column with DB grammar.
181			*
182			* @param string $column
183			* @return string
184			*/
185			protected function wrap($column)
186			{
187			return $this->connection->getQueryGrammar()->wrap($column);
188			}
189
190			/**
191			* Wrap a column and cast based on database driver.
192			*
193			* @param string $column
194			* @return string
195			*/
196			protected function castColumn($column)
197			{
198			switch ($this->connection->getDriverName()) {
199			case 'pgsql':
200			return 'CAST(' . $column . ' as TEXT)';
201			case 'firebird':
202			return 'CAST(' . $column . ' as VARCHAR(255))';
203			default:
204			return $column;
205			}
206			}
207
208			/**
209			* Prepare search keyword based on configurations.
210			*
211			* @param string $keyword
212			* @return string
213			*/
214			protected function prepareKeyword($keyword)
215			{
216			if ($this->config->isCaseInsensitive()) {
217			$keyword = Str::lower($keyword);
218			}
219
220			if ($this->config->isWildcard()) {
221			$keyword = $this->wildcardLikeString($keyword);
222			}
223
224			if ($this->config->isSmartSearch()) {
225			$keyword = "%$keyword%";
226			}
227
228			return $keyword;
229			}
230
231			/**
232			* Organizes works.
233			*
234			* @param bool $mDataSupport
235			* @return \Illuminate\Http\JsonResponse
236			* @throws \Exception
237			*/
238			public function make($mDataSupport = false)
239			{
240			try {
241			$this->totalRecords = $this->totalCount();
242
243			if ($this->totalRecords) {
244			$this->filterRecords();
245			$this->ordering();
246			$this->paginate();
247			}
248
249			$data = $this->transform($this->getProcessedData($mDataSupport));
250
251			return $this->render($data);
252			} catch (\Exception $exception) {
253			return $this->errorResponse($exception);
254			}
255			}
256
257			/**
258			* Count total items.
259			*
260			* @return integer
261			*/
262			public function totalCount()
263			{
264			return $this->totalRecords ? $this->totalRecords : $this->count();
265			}
266
267			/**
268			* Counts current query.
269			*
270			* @return int
271			*/
272			public function count()
273			{
274			$builder = $this->prepareCountQuery();
275			$table = $this->connection->raw('(' . $builder->toSql() . ') count_row_table');
276
277			return $this->connection->table($table)
278			->setBindings($builder->getBindings())
279			->count();
280			}
281
282			/**
283			* Prepare count query builder.
284			*
285			* @return \Illuminate\Database\Query\Builder
286			*/
287			protected function prepareCountQuery()
288			{
289			$builder = clone $this->query;
290
291			if ($this->isComplexQuery($builder)) {
292			$row_count = $this->wrap('row_count');
293			$builder->select($this->connection->raw("'1' as {$row_count}"));
294			}
295
296			return $builder;
297			}
298
299			/**
300			* Check if builder query uses complex sql.
301			*
302			* @param \Illuminate\Database\Query\Builder $builder
303			* @return bool
304			*/
305			protected function isComplexQuery($builder)
306			{
307			return !Str::contains(Str::lower($builder->toSql()), ['union', 'having', 'distinct', 'order by', 'group by']);
308			}
309
310			/**
311			* Perform sorting of columns.
312			*
313			* @return void
314			*/
315			public function ordering()
316			{
317			if ($this->orderCallback) {
318			call_user_func($this->orderCallback, $this->getBaseQueryBuilder());
319
320			return;
321			}
322
323			foreach ($this->request->orderableColumns() as $orderable) {
324			$column = $this->getColumnName($orderable['column'], true);
325
326			if ($this->isBlacklisted($column) && !$this->hasCustomOrder($column)) {
327			continue;
328			}
329
330			if ($this->hasCustomOrder($column)) {
331			$this->applyOrderColumn($column, $orderable);
332			} else {
333			$valid = 1;
334			if (count(explode('.', $column)) > 1) {
335			$eagerLoads = $this->getEagerLoads();
336			$parts = explode('.', $column);
337			$relationColumn = array_pop($parts);
338			$relation = implode('.', $parts);
339
340			if (in_array($relation, $eagerLoads)) {
341			// Loop for nested relations
342			// This code is check morph many or not.
343			// If one of nested relation is MorphToMany
344			// we will call joinEagerLoadedColumn.
345			$lastQuery = $this->query;
346			$isMorphToMany = false;
347			foreach (explode('.', $relation) as $eachRelation) {
348			$relationship = $lastQuery->getRelation($eachRelation);
349			if (!($relationship instanceof MorphToMany)) {
350			$isMorphToMany = true;
351			}
352			$lastQuery = $relationship;
353			}
354			if ($isMorphToMany) {
355			$column = $this->joinEagerLoadedColumn($relation, $relationColumn);
356			} else {
357			$valid = 0;
358			}
359			}
360			}
361
362			if ($valid == 1) {
363			if ($this->nullsLast) {
364			$this->getBaseQueryBuilder()->orderByRaw($this->getNullsLastSql($column,
365			$orderable['direction']));
366			} else {
367			$this->getBaseQueryBuilder()->orderBy($column, $orderable['direction']);
368			}
369			}
370			}
371			}
372			}
373
374			/**
375			* Check if column has custom sort handler.
376			*
377			* @param string $column
378			* @return bool
379			*/
380			protected function hasCustomOrder($column)
381			{
382			return isset($this->columnDef['order'][$column]);
383			}
384
385			/**
386			* Apply orderColumn custom query.
387			*
388			* @param string $column
389			* @param array $orderable
390			*/
391			protected function applyOrderColumn($column, $orderable): void
392			{
393			$sql = $this->columnDef['order'][$column]['sql'];
394			$sql = str_replace('$1', $orderable['direction'], $sql);
395			$bindings = $this->columnDef['order'][$column]['bindings'];
396			$this->query->orderByRaw($sql, $bindings);
397			}
398
399			/**
400			* Get eager loads keys if eloquent.
401			*
402			* @return array
403			*/
404			protected function getEagerLoads()
405			{
406			if ($this->query instanceof EloquentBuilder) {
407			return array_keys($this->query->getEagerLoads());
408			}
409
410			return [];
411			}
412
413			/**
414			* Join eager loaded relation and get the related column name.
415			*
416			* @param string $relation
417			* @param string $relationColumn
418			* @return string
419			*/
420			protected function joinEagerLoadedColumn($relation, $relationColumn)
421			{
422			$table = '';
423			$lastQuery = $this->query;
424			foreach (explode('.', $relation) as $eachRelation) {
425			$model = $lastQuery->getRelation($eachRelation);
426			switch (true) {
427			case $model instanceof BelongsToMany:
428			$pivot = $model->getTable();
429			$pivotPK = $model->getExistenceCompareKey();
430			$pivotFK = $model->getQualifiedParentKeyName();
431			$this->performJoin($pivot, $pivotPK, $pivotFK);
432
433			$related = $model->getRelated();
434			$table = $related->getTable();
435			$tablePK = $related->getForeignKey();
436			$foreign = $pivot . '.' . $tablePK;
437			$other = $related->getQualifiedKeyName();
438
439			$lastQuery->addSelect($table . '.' . $relationColumn);
440			$this->performJoin($table, $foreign, $other);
441
442			break;
443
444			case $model instanceof HasOneOrMany:
445			$table = $model->getRelated()->getTable();
446			$foreign = $model->getQualifiedForeignKeyName();
447			$other = $model->getQualifiedParentKeyName();
448			break;
449
450			case $model instanceof BelongsTo:
451			$table = $model->getRelated()->getTable();
452			$foreign = $model->getQualifiedForeignKey();
453			$other = $model->getQualifiedOwnerKeyName();
454			break;
455
456			default:
457			$table = $model->getRelated()->getTable();
458			$foreign = $model->getQualifiedForeignKey();
459			$other = $model->getQualifiedOtherKeyName();
460			}
461			$this->performJoin($table, $foreign, $other);
462			$lastQuery = $model->getQuery();
463			}
464
465			return $table . '.' . $relationColumn;
466			}
467
468			/**
469			* Perform join query.
470			*
471			* @param string $table
472			* @param string $foreign
473			* @param string $other
474			*/
475			protected function performJoin($table, $foreign, $other)
476			{
477			$joins = [];
478			foreach ((array) $this->getBaseQueryBuilder()->joins as $key => $join) {
479			$joins[] = $join->table;
480			}
481
482			if (!in_array($table, $joins)) {
483			$this->getBaseQueryBuilder()->leftJoin($table, $foreign, '=', $other);
484			}
485			}
486
487			/**
488			* Get NULLS LAST SQL.
489			*
490			* @param string $column
491			* @param string $direction
492			* @return string
493			*/
494			protected function getNullsLastSql($column, $direction)
495			{
496			$sql = $this->config->get('datatables.nulls_last_sql', '%s %s NULLS LAST');
497
498			return sprintf($sql, $column, $direction);
499			}
500
501			/**
502			* Perform column search.
503			*
504			* @return void
505			*/
506			public function columnSearch()
507			{
508			$columns = $this->request->columns();
509
510			foreach ($columns as $index => $column) {
511			if (!$this->request->isColumnSearchable($index)) {
512			continue;
513			}
514
515			$column = $this->getColumnName($index);
516
517			if ($this->hasCustomFilter($column)) {
518			$keyword = $this->getColumnSearchKeyword($index, $raw = true);
519			$this->applyFilterColumn($this->query, $column, $keyword);
520			} else {
521			if (count(explode('.', $column)) > 1) {
522			$eagerLoads = $this->getEagerLoads();
523			$parts = explode('.', $column);
524			$relationColumn = array_pop($parts);
525			$relation = implode('.', $parts);
526			if (in_array($relation, $eagerLoads)) {
527			$column = $this->joinEagerLoadedColumn($relation, $relationColumn);
528			}
529			}
530
531			$keyword = $this->getColumnSearchKeyword($index);
532			$this->compileColumnSearch($index, $column, $keyword);
533			}
534
535			$this->isFilterApplied = true;
536			}
537			}
538
539			/**
540			* Get column keyword to use for search.
541			*
542			* @param int $i
543			* @param bool $raw
544			* @return string
545			*/
546			protected function getColumnSearchKeyword($i, $raw = false)
547			{
548			$keyword = $this->request->columnKeyword($i);
549			if ($raw \|\| $this->request->isRegex($i)) {
550			return $keyword;
551			}
552
553			return $this->setupKeyword($keyword);
554			}
555
556			/**
557			* Compile queries for column search.
558			*
559			* @param int $i
560			* @param string $column
561			* @param string $keyword
562			*/
563			protected function compileColumnSearch($i, $column, $keyword)
564			{
565			if ($this->request->isRegex($i)) {
566			$column = strstr($column, '(') ? $this->connection->raw($column) : $column;
567			$this->regexColumnSearch($column, $keyword);
568			} else {
569			$this->compileQuerySearch($this->query, $column, $keyword, '');
570			}
571			}
572
573			/**
574			* Compile regex query column search.
575			*
576			* @param mixed $column
577			* @param string $keyword
578			*/
579			protected function regexColumnSearch($column, $keyword)
580			{
581			switch ($this->connection->getDriverName()) {
582			case 'oracle':
583			$sql = !$this->config
584			->isCaseInsensitive() ? 'REGEXP_LIKE( ' . $column . ' , ? )' : 'REGEXP_LIKE( LOWER(' . $column . ') , ?, \'i\' )';
585			break;
586
587			case 'pgsql':
588			$sql = !$this->config->isCaseInsensitive() ? $column . ' ~ ?' : $column . ' ~* ? ';
589			break;
590
591			default:
592			$sql = !$this->config
593			->isCaseInsensitive() ? $column . ' REGEXP ?' : 'LOWER(' . $column . ') REGEXP ?';
594			$keyword = Str::lower($keyword);
595			}
596
597			$this->query->whereRaw($sql, [$keyword]);
598			}
599
600			/**
601			* Perform pagination.
602			*
603			* @return void
604			*/
605			public function paging()
606			{
607			$this->query->skip($this->request->input('start'))
608			->take((int) $this->request->input('length') > 0 ? $this->request->input('length') : 10);
609			}
610
611			/**
612			* Get paginated results.
613			*
614			* @return \Illuminate\Support\Collection
615			*/
616			public function results()
617			{
618			return $this->query->get();
619			}
620
621			/**
622			* Add column in collection.
623			*
624			* @param string $name
625			* @param string\|callable $content
626			* @param bool\|int $order
627			* @return \Yajra\Datatables\Engines\BaseEngine\|\Yajra\Datatables\Engines\QueryBuilderEngine
628			*/
629			public function addColumn($name, $content, $order = false)
630			{
631			$this->pushToBlacklist($name);
632
633			return parent::addColumn($name, $content, $order);
634			}
635
636			/**
637			* Get query builder instance.
638			*
639			* @return \Illuminate\Database\Eloquent\Builder\|\Illuminate\Database\Query\Builder
640			*/
641			public function getQuery()
642			{
643			return $this->query;
644			}
645
646			/**
647			* Append debug parameters on output.
648			*
649			* @param array $output
650			* @return array
651			*/
652			protected function showDebugger(array $output)
653			{
654			$output['queries'] = $this->connection->getQueryLog();
655			$output['input'] = $this->request->all();
656
657			return $output;
658			}
659			}
660

yajra / laravel-datatables

Push — master ( d36753...bfa7ec )

QueryBuilderEngine::globalSearch() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like