QueryDataTable::searchPanesSearch() - Code Metrics - Inspection of "Merge branch '9.0'" - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( b1bb78...82c1ec )

by Arjay

created 2020-11-03 09:58 UTC

QueryDataTable::searchPanesSearch() A

↳ Parent: QueryDataTable

Complexity

Conditions	4
Paths	4

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	18
c	0
b	0
f	0
cc	4
nc	4
nop	0
rs	9.6666

<?php

namespace Yajra\DataTables;

use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Str;
use Yajra\DataTables\Utilities\Helper;

class QueryDataTable extends DataTableAbstract
{
    /**
     * Builder object.
     *
     * @var \Illuminate\Database\Query\Builder
     */
    protected $query;

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

    /**
     * Flag for ordering NULLS LAST option.
     *
     * @var bool
     */
    protected $nullsLast = false;

    /**
     * Flag to check if query preparation was already done.
     *
     * @var bool
     */
    protected $prepared = false;

    /**
     * Query callback for custom pagination using limit without offset.
     *
     * @var callable
     */
    protected $limitCallback;

    /**
     * Flag to skip total records count query.
     *
     * @var bool
     */
    protected $skipTotalRecords = false;

    /**
     * Flag to keep the select bindings.
     *
     * @var bool
     */
    protected $keepSelectBindings = false;

    /**
     * Can the DataTable engine be created with these parameters.
     *
     * @param mixed $source
     * @return bool
     */
    public static function canCreate($source)
    {
        return $source instanceof Builder;
    }

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

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

            $results   = $this->results();
            $processed = $this->processResults($results, $mDataSupport);
            $data      = $this->transform($results, $processed);

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

    /**
     * Perform search using search pane values.
     */
    protected function searchPanesSearch()
    {
        $columns = $this->request->get('searchPanes', []);

        foreach ($columns as $column => $values) {
            if ($this->isBlacklisted($column)) {
                continue;
            }

            if ($callback = data_get($this->searchPanes, $column . '.builder')) {
                $callback($this->getBaseQueryBuilder(), $values);
            } else {
                $this->getBaseQueryBuilder()->whereIn($column, $values);
            }

            $this->isFilterApplied = true;
        }
    }

    /**
     * Prepare query by executing count, filter, order and paginate.
     */
    protected function prepareQuery()
    {
        if (! $this->prepared) {
            $this->totalRecords = $this->totalCount();

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

        $this->prepared = true;
    }

    /**
     * Skip total records and set the recordsTotal equals to recordsFiltered.
     * This will improve the performance by skipping the total count query.
     *
     * @return $this
     */
    public function skipTotalRecords()
    {
        $this->skipTotalRecords = true;

        return $this;
    }

    /**
     * Keep the select bindings.
     *
     * @return $this
     */
    public function keepSelectBindings()
    {
        $this->keepSelectBindings = true;

        return $this;
    }

    /**
     * Count total items.
     *
     * @return int
     */
    public function totalCount()
    {
        if ($this->skipTotalRecords) {
            $this->isFilterApplied = true;

            return 1;
        }

        return $this->totalRecords ? $this->totalRecords : $this->count();
    }

    /**
     * Count filtered items.
     *
     * @return int
     */
    protected function filteredCount()
    {
        $this->filteredRecords = $this->filteredRecords ?: $this->count();
        if ($this->skipTotalRecords) {
            $this->totalRecords = $this->filteredRecords;
        }

        return $this->filteredRecords;
    }

    /**
     * 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\Eloquent\Builder|\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}"));
            if (! $this->keepSelectBindings) {
                $builder->setBindings([], 'select');
            }
        }

        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']);
    }

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

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

    /**
     * Get filtered, ordered and paginated query.
     *
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder
     */
    public function getFilteredQuery()
    {
        $this->prepareQuery();

        return $this->getQuery();
    }

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

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

        foreach ($columns as $index => $column) {
            $column = $this->getColumnName($index);

            if (! $this->request->isColumnSearchable($index) || $this->isBlacklisted($column) && ! $this->hasFilterColumn($column)) {
                continue;
            }

            if ($this->hasFilterColumn($column)) {
                $keyword = $this->getColumnSearchKeyword($index, $raw = true);
                $this->applyFilterColumn($this->getBaseQueryBuilder(), $column, $keyword);
            } else {
                $column  = $this->resolveRelationColumn($column);
                $keyword = $this->getColumnSearchKeyword($index);
                $this->compileColumnSearch($index, $column, $keyword);
            }

            $this->isFilterApplied = true;
        }
    }

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

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

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

        if ($this->query instanceof EloquentBuilder) {
            $builder = $this->query->newModelInstance()->newQuery();
        } else {
            $builder = $this->query->newQuery();
        }

        $callback($builder, $keyword);

        $query->addNestedWhereQuery($this->getBaseQueryBuilder($builder), $boolean);
    }

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

    /**
     * Resolve the proper column name be used.
     *
     * @param string $column
     * @return string
     */
    protected function resolveRelationColumn($column)
    {
        return $column;
    }

    /**
     * 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':
                $column = $this->castColumn($column);
                $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]);
    }

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

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

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

        $query->{$boolean . '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);
    }

    /**
     * Prepare search keyword based on configurations.
     *
     * @param string $keyword
     * @return string
     */
    protected function prepareKeyword($keyword)
    {
        if ($this->config->isStartsWithSearch()) {
            return "$keyword%";
        }

        if ($this->config->isCaseInsensitive()) {
            $keyword = Str::lower($keyword);
        }

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

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

        return $keyword;
    }

    /**
     * Add custom filter handler for the give column.
     *
     * @param string   $column
     * @param callable $callback
     * @return $this
     */
    public function filterColumn($column, callable $callback)
    {
        $this->columnDef['filter'][$column] = ['method' => $callback];

        return $this;
    }

    /**
     * Order each given columns versus the given custom sql.
     *
     * @param array  $columns
     * @param string $sql
     * @param array  $bindings
     * @return $this
     */
    public function orderColumns(array $columns, $sql, $bindings = [])
    {
        foreach ($columns as $column) {
            $this->orderColumn($column, str_replace(':column', $column, $sql), $bindings);
        }

        return $this;
    }

    /**
     * Override default column ordering.
     *
     * @param string $column
     * @param string $sql
     * @param array  $bindings
     * @return $this
     * @internal string $1 Special variable that returns the requested order direction of the column.
     */
    public function orderColumn($column, $sql, $bindings = [])
    {
        $this->columnDef['order'][$column] = compact('sql', 'bindings');

        return $this;
    }

    /**
     * Set datatables to do ordering with NULLS LAST option.
     *
     * @return $this
     */
    public function orderByNullsLast()
    {
        $this->nullsLast = true;

        return $this;
    }

    /**
     * Paginate dataTable using limit without offset
     * with additional where clause via callback.
     *
     * @param callable $callback
     * @return $this
     */
    public function limit(callable $callback)
    {
        $this->limitCallback = $callback;

        return $this;
    }

    /**
     * Perform pagination.
     *
     * @return void
     */
    public function paging()
    {
        $limit = (int) $this->request->input('length') > 0 ? $this->request->input('length') : 10;
        if (is_callable($this->limitCallback)) {
            $this->query->limit($limit);
            call_user_func_array($this->limitCallback, [$this->query]);
        } else {
            $this->query->skip($this->request->input('start'))->take($limit);
        }
    }

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

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

    /**
     * Resolve callback parameter instance.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function resolveCallbackParameter()
    {
        return $this->query;
    }

    /**
     * Perform default query orderBy clause.
     */
    protected function defaultOrdering()
    {
        collect($this->request->orderableColumns())
            ->map(function ($orderable) {
                $orderable['name'] = $this->getColumnName($orderable['column'], true);

                return $orderable;
            })
            ->reject(function ($orderable) {
                return $this->isBlacklisted($orderable['name']) && ! $this->hasOrderColumn($orderable['name']);
            })
            ->each(function ($orderable) {
                $column = $this->resolveRelationColumn($orderable['name']);

                if ($this->hasOrderColumn($column)) {
                    $this->applyOrderColumn($column, $orderable);
                } else {
                    $nullsLastSql = $this->getNullsLastSql($column, $orderable['direction']);
                    $normalSql = $this->wrap($column) . ' ' . $orderable['direction'];
                    $sql = $this->nullsLast ? $nullsLastSql : $normalSql;
                    $this->query->orderByRaw($sql);
                }
            });
    }

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

    /**
     * Apply orderColumn custom query.
     *
     * @param string $column
     * @param array $orderable
     */
    protected function applyOrderColumn($column, $orderable)
    {
        $sql = $this->columnDef['order'][$column]['sql'];
        if ($sql === false) {
            return;
        }

        if (is_callable($sql)) {
            call_user_func($sql, $this->query, $orderable['direction']);
        } else {
            $sql      = str_replace('$1', $orderable['direction'], $sql);
            $bindings = $this->columnDef['order'][$column]['bindings'];
            $this->query->orderByRaw($sql, $bindings);
        }
    }

    /**
     * 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 str_replace(
            [':column', ':direction'],
            [$column, $direction],
            sprintf($sql, $column, $direction)
        );
    }

    /**
     * Perform global search for the given keyword.
     *
     * @param string $keyword
     */
    protected function globalSearch($keyword)
    {
        $this->query->where(function ($query) use ($keyword) {
            collect($this->request->searchableColumnIndex())
                ->map(function ($index) {
                    return $this->getColumnName($index);
                })
                ->reject(function ($column) {
                    return $this->isBlacklisted($column) && ! $this->hasFilterColumn($column);
                })
                ->each(function ($column) use ($keyword, $query) {
                    if ($this->hasFilterColumn($column)) {
                        $this->applyFilterColumn($query, $column, $keyword, 'or');
                    } else {
                        $this->compileQuerySearch($query, $column, $keyword);
                    }

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

    /**
     * Append debug parameters on output.
     *
     * @param  array $output
     * @return array
     */
    protected function showDebugger(array $output)
    {
        $query_log = $this->connection->getQueryLog();
        array_walk_recursive($query_log, function (&$item, $key) {

            $item = utf8_encode($item);
        });

        $output['queries'] = $query_log;
        $output['input']   = $this->request->all();

        return $output;
    }

    /**
     * Attach custom with meta on response.
     *
     * @param array $data
     * @return array
     */
    protected function attachAppends(array $data)
    {
        $appends = [];
        foreach ($this->appends as $key => $value) {
            if (is_callable($value)) {
                $appends[$key] = value($value($this->getFilteredQuery()));
            } else {
                $appends[$key] = $value;
            }
        }

        return array_merge($data, $appends);
    }
}


1			<?php
2
3			namespace Yajra\DataTables;
4
5			use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
6			use Illuminate\Database\Query\Builder;
7			use Illuminate\Database\Query\Expression;
8			use Illuminate\Support\Str;
9			use Yajra\DataTables\Utilities\Helper;
10
11			class QueryDataTable extends DataTableAbstract
12			{
13			/**
14			* Builder object.
15			*
16			* @var \Illuminate\Database\Query\Builder
17			*/
18			protected $query;
19
20			/**
21			* Database connection used.
22			*
23			* @var \Illuminate\Database\Connection
24			*/
25			protected $connection;
26
27			/**
28			* Flag for ordering NULLS LAST option.
29			*
30			* @var bool
31			*/
32			protected $nullsLast = false;
33
34			/**
35			* Flag to check if query preparation was already done.
36			*
37			* @var bool
38			*/
39			protected $prepared = false;
40
41			/**
42			* Query callback for custom pagination using limit without offset.
43			*
44			* @var callable
45			*/
46			protected $limitCallback;
47
48			/**
49			* Flag to skip total records count query.
50			*
51			* @var bool
52			*/
53			protected $skipTotalRecords = false;
54
55			/**
56			* Flag to keep the select bindings.
57			*
58			* @var bool
59			*/
60			protected $keepSelectBindings = false;
61
62			/**
63			* Can the DataTable engine be created with these parameters.
64			*
65			* @param mixed $source
66			* @return bool
67			*/
68			public static function canCreate($source)
69			{
70			return $source instanceof Builder;
71			}
72
73			/**
74			* @param \Illuminate\Database\Query\Builder $builder
75			*/
76			public function __construct(Builder $builder)
77			{
78			$this->query = $builder;
79			$this->request = app('datatables.request');
80			$this->config = app('datatables.config');
81			$this->columns = $builder->columns;
82			$this->connection = $builder->getConnection();
83			if ($this->config->isDebugging()) {
84			$this->connection->enableQueryLog();
85			}
86			}
87
88			/**
89			* Organizes works.
90			*
91			* @param bool $mDataSupport
92			* @return \Illuminate\Http\JsonResponse
93			* @throws \Exception
94			*/
95			public function make($mDataSupport = true)
96			{
97			try {
98			$this->prepareQuery();
99
100			$results = $this->results();
101			$processed = $this->processResults($results, $mDataSupport);
102			$data = $this->transform($results, $processed);
103
104			return $this->render($data);
105			} catch (\Exception $exception) {
106			return $this->errorResponse($exception);
107			}
108			}
109
110			/**
111			* Perform search using search pane values.
112			*/
113			protected function searchPanesSearch()
114			{
115			$columns = $this->request->get('searchPanes', []);
116
117			foreach ($columns as $column => $values) {
118			if ($this->isBlacklisted($column)) {
119			continue;
120			}
121
122			if ($callback = data_get($this->searchPanes, $column . '.builder')) {
123			$callback($this->getBaseQueryBuilder(), $values);
124			} else {
125			$this->getBaseQueryBuilder()->whereIn($column, $values);
126			}
127
128			$this->isFilterApplied = true;
129			}
130			}
131
132			/**
133			* Prepare query by executing count, filter, order and paginate.
134			*/
135			protected function prepareQuery()
136			{
137			if (! $this->prepared) {
138			$this->totalRecords = $this->totalCount();
139
140			if ($this->totalRecords) {
141			$this->filterRecords();
142			$this->ordering();
143			$this->paginate();
144			}
145			}
146
147			$this->prepared = true;
148			}
149
150			/**
151			* Skip total records and set the recordsTotal equals to recordsFiltered.
152			* This will improve the performance by skipping the total count query.
153			*
154			* @return $this
155			*/
156			public function skipTotalRecords()
157			{
158			$this->skipTotalRecords = true;
159
160			return $this;
161			}
162
163			/**
164			* Keep the select bindings.
165			*
166			* @return $this
167			*/
168			public function keepSelectBindings()
169			{
170			$this->keepSelectBindings = true;
171
172			return $this;
173			}
174
175			/**
176			* Count total items.
177			*
178			* @return int
179			*/
180			public function totalCount()
181			{
182			if ($this->skipTotalRecords) {
183			$this->isFilterApplied = true;
184
185			return 1;
186			}
187
188			return $this->totalRecords ? $this->totalRecords : $this->count();
189			}
190
191			/**
192			* Count filtered items.
193			*
194			* @return int
195			*/
196			protected function filteredCount()
197			{
198			$this->filteredRecords = $this->filteredRecords ?: $this->count();
199			if ($this->skipTotalRecords) {
200			$this->totalRecords = $this->filteredRecords;
201			}
202
203			return $this->filteredRecords;
204			}
205
206			/**
207			* Counts current query.
208			*
209			* @return int
210			*/
211			public function count()
212			{
213			$builder = $this->prepareCountQuery();
214			$table = $this->connection->raw('(' . $builder->toSql() . ') count_row_table');
215
216			return $this->connection->table($table)
217			->setBindings($builder->getBindings())
218			->count();
219			}
220
221			/**
222			* Prepare count query builder.
223			*
224			* @return \Illuminate\Database\Eloquent\Builder\|\Illuminate\Database\Query\Builder
225			*/
226			protected function prepareCountQuery()
227			{
228			$builder = clone $this->query;
229
230			if (! $this->isComplexQuery($builder)) {
231			$row_count = $this->wrap('row_count');
232			$builder->select($this->connection->raw("'1' as {$row_count}"));
233			if (! $this->keepSelectBindings) {
234			$builder->setBindings([], 'select');
235			}
236			}
237
238			return $builder;
239			}
240
241			/**
242			* Check if builder query uses complex sql.
243			*
244			* @param \Illuminate\Database\Query\Builder $builder
245			* @return bool
246			*/
247			protected function isComplexQuery($builder)
248			{
249			return Str::contains(Str::lower($builder->toSql()), ['union', 'having', 'distinct', 'order by', 'group by']);
250			}
251
252			/**
253			* Wrap column with DB grammar.
254			*
255			* @param string $column
256			* @return string
257			*/
258			protected function wrap($column)
259			{
260			return $this->connection->getQueryGrammar()->wrap($column);
261			}
262
263			/**
264			* Get paginated results.
265			*
266			* @return \Illuminate\Support\Collection
267			*/
268			public function results()
269			{
270			return $this->query->get();
271			}
272
273			/**
274			* Get filtered, ordered and paginated query.
275			*
276			* @return \Illuminate\Database\Eloquent\Builder\|\Illuminate\Database\Query\Builder
277			*/
278			public function getFilteredQuery()
279			{
280			$this->prepareQuery();
281
282			return $this->getQuery();
283			}
284
285			/**
286			* Get query builder instance.
287			*
288			* @return \Illuminate\Database\Eloquent\Builder\|\Illuminate\Database\Query\Builder
289			*/
290			public function getQuery()
291			{
292			return $this->query;
293			}
294
295			/**
296			* Perform column search.
297			*
298			* @return void
299			*/
300			public function columnSearch()
301			{
302			$columns = $this->request->columns();
303
304			foreach ($columns as $index => $column) {
305			$column = $this->getColumnName($index);
306
307			if (! $this->request->isColumnSearchable($index) \|\| $this->isBlacklisted($column) && ! $this->hasFilterColumn($column)) {
308			continue;
309			}
310
311			if ($this->hasFilterColumn($column)) {
312			$keyword = $this->getColumnSearchKeyword($index, $raw = true);
313			$this->applyFilterColumn($this->getBaseQueryBuilder(), $column, $keyword);
314			} else {
315			$column = $this->resolveRelationColumn($column);
316			$keyword = $this->getColumnSearchKeyword($index);
317			$this->compileColumnSearch($index, $column, $keyword);
318			}
319
320			$this->isFilterApplied = true;
321			}
322			}
323
324			/**
325			* Check if column has custom filter handler.
326			*
327			* @param string $columnName
328			* @return bool
329			*/
330			public function hasFilterColumn($columnName)
331			{
332			return isset($this->columnDef['filter'][$columnName]);
333			}
334
335			/**
336			* Get column keyword to use for search.
337			*
338			* @param int $i
339			* @param bool $raw
340			* @return string
341			*/
342			protected function getColumnSearchKeyword($i, $raw = false)
343			{
344			$keyword = $this->request->columnKeyword($i);
345			if ($raw \|\| $this->request->isRegex($i)) {
346			return $keyword;
347			}
348
349			return $this->setupKeyword($keyword);
350			}
351
352			/**
353			* Apply filterColumn api search.
354			*
355			* @param mixed $query
356			* @param string $columnName
357			* @param string $keyword
358			* @param string $boolean
359			*/
360			protected function applyFilterColumn($query, $columnName, $keyword, $boolean = 'and')
361			{
362			$query = $this->getBaseQueryBuilder($query);
363			$callback = $this->columnDef['filter'][$columnName]['method'];
364
365			if ($this->query instanceof EloquentBuilder) {
366			$builder = $this->query->newModelInstance()->newQuery();
367			} else {
368			$builder = $this->query->newQuery();
369			}
370
371			$callback($builder, $keyword);
372
373			$query->addNestedWhereQuery($this->getBaseQueryBuilder($builder), $boolean);
374			}
375
376			/**
377			* Get the base query builder instance.
378			*
379			* @param mixed $instance
380			* @return \Illuminate\Database\Query\Builder
381			*/
382			protected function getBaseQueryBuilder($instance = null)
383			{
384			if (! $instance) {
385			$instance = $this->query;
386			}
387
388			if ($instance instanceof EloquentBuilder) {
389			return $instance->getQuery();
390			}
391
392			return $instance;
393			}
394
395			/**
396			* Resolve the proper column name be used.
397			*
398			* @param string $column
399			* @return string
400			*/
401			protected function resolveRelationColumn($column)
402			{
403			return $column;
404			}
405
406			/**
407			* Compile queries for column search.
408			*
409			* @param int $i
410			* @param string $column
411			* @param string $keyword
412			*/
413			protected function compileColumnSearch($i, $column, $keyword)
414			{
415			if ($this->request->isRegex($i)) {
416			$column = strstr($column, '(') ? $this->connection->raw($column) : $column;
417			$this->regexColumnSearch($column, $keyword);
418			} else {
419			$this->compileQuerySearch($this->query, $column, $keyword, '');
420			}
421			}
422
423			/**
424			* Compile regex query column search.
425			*
426			* @param mixed $column
427			* @param string $keyword
428			*/
429			protected function regexColumnSearch($column, $keyword)
430			{
431			switch ($this->connection->getDriverName()) {
432			case 'oracle':
433			$sql = ! $this->config->isCaseInsensitive()
434			? 'REGEXP_LIKE( ' . $column . ' , ? )'
435			: 'REGEXP_LIKE( LOWER(' . $column . ') , ?, \'i\' )';
436			break;
437
438			case 'pgsql':
439			$column = $this->castColumn($column);
440			$sql = ! $this->config->isCaseInsensitive() ? $column . ' ~ ?' : $column . ' ~* ? ';
441			break;
442
443			default:
444			$sql = ! $this->config->isCaseInsensitive()
445			? $column . ' REGEXP ?'
446			: 'LOWER(' . $column . ') REGEXP ?';
447			$keyword = Str::lower($keyword);
448			}
449
450			$this->query->whereRaw($sql, [$keyword]);
451			}
452
453			/**
454			* Wrap a column and cast based on database driver.
455			*
456			* @param string $column
457			* @return string
458			*/
459			protected function castColumn($column)
460			{
461			switch ($this->connection->getDriverName()) {
462			case 'pgsql':
463			return 'CAST(' . $column . ' as TEXT)';
464			case 'firebird':
465			return 'CAST(' . $column . ' as VARCHAR(255))';
466			default:
467			return $column;
468			}
469			}
470
471			/**
472			* Compile query builder where clause depending on configurations.
473			*
474			* @param mixed $query
475			* @param string $column
476			* @param string $keyword
477			* @param string $boolean
478			*/
479			protected function compileQuerySearch($query, $column, $keyword, $boolean = 'or')
480			{
481			$column = $this->addTablePrefix($query, $column);
482			$column = $this->castColumn($column);
483			$sql = $column . ' LIKE ?';
484
485			if ($this->config->isCaseInsensitive()) {
486			$sql = 'LOWER(' . $column . ') LIKE ?';
487			}
488
489			$query->{$boolean . 'WhereRaw'}($sql, [$this->prepareKeyword($keyword)]);
490			}
491
492			/**
493			* Patch for fix about ambiguous field.
494			* Ambiguous field error will appear when query use join table and search with keyword.
495			*
496			* @param mixed $query
497			* @param string $column
498			* @return string
499			*/
500			protected function addTablePrefix($query, $column)
501			{
502			if (strpos($column, '.') === false) {
503			$q = $this->getBaseQueryBuilder($query);
504			if (! $q->from instanceof Expression) {
505			$column = $q->from . '.' . $column;
506			}
507			}
508
509			return $this->wrap($column);
510			}
511
512			/**
513			* Prepare search keyword based on configurations.
514			*
515			* @param string $keyword
516			* @return string
517			*/
518			protected function prepareKeyword($keyword)
519			{
520			if ($this->config->isStartsWithSearch()) {
521			return "$keyword%";
522			}
523
524			if ($this->config->isCaseInsensitive()) {
525			$keyword = Str::lower($keyword);
526			}
527
528			if ($this->config->isWildcard()) {
529			$keyword = Helper::wildcardLikeString($keyword);
530			}
531
532			if ($this->config->isSmartSearch()) {
533			$keyword = "%$keyword%";
534			}
535
536			return $keyword;
537			}
538
539			/**
540			* Add custom filter handler for the give column.
541			*
542			* @param string $column
543			* @param callable $callback
544			* @return $this
545			*/
546			public function filterColumn($column, callable $callback)
547			{
548			$this->columnDef['filter'][$column] = ['method' => $callback];
549
550			return $this;
551			}
552
553			/**
554			* Order each given columns versus the given custom sql.
555			*
556			* @param array $columns
557			* @param string $sql
558			* @param array $bindings
559			* @return $this
560			*/
561			public function orderColumns(array $columns, $sql, $bindings = [])
562			{
563			foreach ($columns as $column) {
564			$this->orderColumn($column, str_replace(':column', $column, $sql), $bindings);
565			}
566
567			return $this;
568			}
569
570			/**
571			* Override default column ordering.
572			*
573			* @param string $column
574			* @param string $sql
575			* @param array $bindings
576			* @return $this
577			* @internal string $1 Special variable that returns the requested order direction of the column.
578			*/
579			public function orderColumn($column, $sql, $bindings = [])
580			{
581			$this->columnDef['order'][$column] = compact('sql', 'bindings');
582
583			return $this;
584			}
585
586			/**
587			* Set datatables to do ordering with NULLS LAST option.
588			*
589			* @return $this
590			*/
591			public function orderByNullsLast()
592			{
593			$this->nullsLast = true;
594
595			return $this;
596			}
597
598			/**
599			* Paginate dataTable using limit without offset
600			* with additional where clause via callback.
601			*
602			* @param callable $callback
603			* @return $this
604			*/
605			public function limit(callable $callback)
606			{
607			$this->limitCallback = $callback;
608
609			return $this;
610			}
611
612			/**
613			* Perform pagination.
614			*
615			* @return void
616			*/
617			public function paging()
618			{
619			$limit = (int) $this->request->input('length') > 0 ? $this->request->input('length') : 10;
620			if (is_callable($this->limitCallback)) {
621			$this->query->limit($limit);
622			call_user_func_array($this->limitCallback, [$this->query]);
623			} else {
624			$this->query->skip($this->request->input('start'))->take($limit);
625			}
626			}
627
628			/**
629			* Add column in collection.
630			*
631			* @param string $name
632			* @param string\|callable $content
633			* @param bool\|int $order
634			* @return $this
635			*/
636			public function addColumn($name, $content, $order = false)
637			{
638			$this->pushToBlacklist($name);
639
640			return parent::addColumn($name, $content, $order);
641			}
642
643			/**
644			* Resolve callback parameter instance.
645			*
646			* @return \Illuminate\Database\Query\Builder
647			*/
648			protected function resolveCallbackParameter()
649			{
650			return $this->query;
651			}
652
653			/**
654			* Perform default query orderBy clause.
655			*/
656			protected function defaultOrdering()
657			{
658			collect($this->request->orderableColumns())
659			->map(function ($orderable) {
660			$orderable['name'] = $this->getColumnName($orderable['column'], true);
661
662			return $orderable;
663			})
664			->reject(function ($orderable) {
665			return $this->isBlacklisted($orderable['name']) && ! $this->hasOrderColumn($orderable['name']);
666			})
667			->each(function ($orderable) {
668			$column = $this->resolveRelationColumn($orderable['name']);
669
670			if ($this->hasOrderColumn($column)) {
671			$this->applyOrderColumn($column, $orderable);
672			} else {
673			$nullsLastSql = $this->getNullsLastSql($column, $orderable['direction']);
674			$normalSql = $this->wrap($column) . ' ' . $orderable['direction'];
675			$sql = $this->nullsLast ? $nullsLastSql : $normalSql;
676			$this->query->orderByRaw($sql);
677			}
678			});
679			}
680
681			/**
682			* Check if column has custom sort handler.
683			*
684			* @param string $column
685			* @return bool
686			*/
687			protected function hasOrderColumn($column)
688			{
689			return isset($this->columnDef['order'][$column]);
690			}
691
692			/**
693			* Apply orderColumn custom query.
694			*
695			* @param string $column
696			* @param array $orderable
697			*/
698			protected function applyOrderColumn($column, $orderable)
699			{
700			$sql = $this->columnDef['order'][$column]['sql'];
701			if ($sql === false) {
702			return;
703			}
704
705			if (is_callable($sql)) {
706			call_user_func($sql, $this->query, $orderable['direction']);
707			} else {
708			$sql = str_replace('$1', $orderable['direction'], $sql);
709			$bindings = $this->columnDef['order'][$column]['bindings'];
710			$this->query->orderByRaw($sql, $bindings);
711			}
712			}
713
714			/**
715			* Get NULLS LAST SQL.
716			*
717			* @param string $column
718			* @param string $direction
719			* @return string
720			*/
721			protected function getNullsLastSql($column, $direction)
722			{
723			$sql = $this->config->get('datatables.nulls_last_sql', '%s %s NULLS LAST');
724
725			return str_replace(
726			[':column', ':direction'],
727			[$column, $direction],
728			sprintf($sql, $column, $direction)
729			);
730			}
731
732			/**
733			* Perform global search for the given keyword.
734			*
735			* @param string $keyword
736			*/
737			protected function globalSearch($keyword)
738			{
739			$this->query->where(function ($query) use ($keyword) {
740			collect($this->request->searchableColumnIndex())
741			->map(function ($index) {
742			return $this->getColumnName($index);
743			})
744			->reject(function ($column) {
745			return $this->isBlacklisted($column) && ! $this->hasFilterColumn($column);
746			})
747			->each(function ($column) use ($keyword, $query) {
748			if ($this->hasFilterColumn($column)) {
749			$this->applyFilterColumn($query, $column, $keyword, 'or');
750			} else {
751			$this->compileQuerySearch($query, $column, $keyword);
752			}
753
754			$this->isFilterApplied = true;
755			});
756			});
757			}
758
759			/**
760			* Append debug parameters on output.
761			*
762			* @param array $output
763			* @return array
764			*/
765			protected function showDebugger(array $output)
766			{
767			$query_log = $this->connection->getQueryLog();
768			array_walk_recursive($query_log, function (&$item, $key) {
			0 ignored issues – show Unused Code introduced 2019-05-21 05:18 UTC by Report Bug Copy Issue Report The parameter `$key` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
769			$item = utf8_encode($item);
770			});
771
772			$output['queries'] = $query_log;
773			$output['input'] = $this->request->all();
774
775			return $output;
776			}
777
778			/**
779			* Attach custom with meta on response.
780			*
781			* @param array $data
782			* @return array
783			*/
784			protected function attachAppends(array $data)
785			{
786			$appends = [];
787			foreach ($this->appends as $key => $value) {
788			if (is_callable($value)) {
789			$appends[$key] = value($value($this->getFilteredQuery()));
790			} else {
791			$appends[$key] = $value;
792			}
793			}
794
795			return array_merge($data, $appends);
796			}
797			}
798

yajra / laravel-datatables

Push — master ( b1bb78...82c1ec )

QueryDataTable::searchPanesSearch() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like