QueryBuilderEngine::orderColumns() - Code Metrics - Inspection of "Pull down methods that are only applicable on quer..." - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( f1ca9b...c69c44 )

by Arjay

created 2017-06-30 03:44 UTC

QueryBuilderEngine::orderColumns() A

↳ Parent: QueryBuilderEngine

Complexity

Conditions	2
Paths	2

Size

Total Lines	8
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	4
nc	2
nop	3
dl	0
loc	8
rs	9.4285
c	0
b	0
f	0

<?php

namespace Yajra\Datatables\Engines;

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

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

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

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

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

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

    /**
     * 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 Builder $query
     * @param string  $columnName
     * @param string  $keyword
     * @param string  $boolean
     */
    protected function applyFilterColumn(Builder $query, $columnName, $keyword, $boolean = 'and')
    {
        $callback = $this->columnDef['filter'][$columnName]['method'];
        $builder  = $query->newQuery();
        $callback($builder, $keyword);
        $query->addNestedWhereQuery($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':
                $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]);
    }

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

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

    /**
     * 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) {
            $query = $this->getBaseQueryBuilder($query);

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

yajra / laravel-datatables

Push — master ( f1ca9b...c69c44 )

QueryBuilderEngine::orderColumns() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like