QueryDataTable::regexColumnSearch() - Code Metrics - Inspection of "Implement #1338 on v8." - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 18c282...3e9b09 )

by Arjay

created 2017-08-24 05:30 UTC

QueryDataTable::regexColumnSearch() B

↳ Parent: QueryDataTable

Complexity

Conditions	6
Paths	6

Size

Total Lines	21
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	6
eloc	15
nc	6
nop	2
dl	0
loc	21
rs	8.7624
c	0
b	0
f	0

<?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;

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

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

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

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

        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) {
            if (!$this->request->isColumnSearchable($index)) {
                continue;
            }

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

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

            $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->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 \Yajra\DataTables\DataTableAbstract|\Yajra\DataTables\Builders\QueryDataTable
     */
    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): 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 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 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)
    {
        $output['queries'] = $this->connection->getQueryLog();
        $output['input']   = $this->request->all();

        return $output;
    }
}


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

yajra / laravel-datatables

Push — master ( 18c282...3e9b09 )

QueryDataTable::regexColumnSearch() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like