QueryBuilderEngine::count() - Code Metrics - Inspection of "Add `getFilteredQuery` api to get the prepared (fi..." - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( e7f8c9...5addd2 )

by Arjay

created 2017-06-30 11:10 UTC

QueryBuilderEngine::count() A

↳ Parent: QueryBuilderEngine

Complexity

Conditions	1
Paths	1

Size

Total Lines	9
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	6
nc	1
nop	0
dl	0
loc	9
rs	9.6666
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;

    /**
     * Flag to check if query preparation was already done.
     *
     * @var bool
     */
    protected $prepared = 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();
        }
    }

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

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

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

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

yajra / laravel-datatables

Push — master ( e7f8c9...5addd2 )

QueryBuilderEngine::count() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like