QueryBuilderEngine::filtering() - Code Metrics - Inspection of "Bump v6.4.0" - yajra/laravel-datatables - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 129599...13da9b )

by Arjay

created 2016-02-10 02:38 UTC

QueryBuilderEngine::filtering() B

↳ Parent: QueryBuilderEngine

Complexity

Conditions	5
Paths	1

Size

Total Lines	37
Code Lines	25

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	2	Features	0

Metric	Value
c	3
b	2
f	0
dl	0
loc	37
rs	8.439
cc	5
eloc	25
nc	1
nop	0

<?php

namespace Yajra\Datatables\Engines;

/**
 * Laravel Datatables Query Builder Engine
 *
 * @package  Laravel
 * @category Package
 * @author   Arjay Angeles <[email protected]>
 */

use Closure;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Str;
use Yajra\Datatables\Contracts\DataTableEngineContract;
use Yajra\Datatables\Helper;
use Yajra\Datatables\Request;

class QueryBuilderEngine extends BaseEngine implements DataTableEngineContract
{
    /**
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \Yajra\Datatables\Request $request
     */
    public function __construct(Builder $builder, Request $request)
    {
        $this->query = $builder;
        $this->init($request, $builder);
    }

    /**
     * Initialize attributes.
     *
     * @param  \Yajra\Datatables\Request $request
     * @param  \Illuminate\Database\Query\Builder $builder
     * @param  string $type
     */
    protected function init($request, $builder, $type = 'builder')

    {
        $this->request    = $request;
        $this->query_type = $type;
        $this->columns    = $builder->columns;
        $this->connection = $builder->getConnection();
        $this->prefix     = $this->connection->getTablePrefix();
        $this->database   = $this->connection->getDriverName();
        if ($this->isDebugging()) {
            $this->connection->enableQueryLog();
        }
    }

    /**
     * @inheritdoc
     */
    public function filter(Closure $callback)
    {
        $this->overrideGlobalSearch($callback, $this->query);

        return $this;
    }

    /**
     * @inheritdoc
     */
    public function make($mDataSupport = false, $orderFirst = false)
    {
        return parent::make($mDataSupport, $orderFirst);
    }

    /**
     * @inheritdoc
     */
    public function totalCount()
    {
        return $this->count();
    }

    /**
     * Counts current query.
     *
     * @return int
     */
    public function count()
    {
        $myQuery = clone $this->query;
        // if its a normal query ( no union, having and distinct word )
        // replace the select with static text to improve performance
        if (! Str::contains(Str::lower($myQuery->toSql()), ['union', 'having', 'distinct', 'order by', 'group by'])) {
            $row_count = $this->connection->getQueryGrammar()->wrap('row_count');
            $myQuery->select($this->connection->raw("'1' as {$row_count}"));
        }

        return $this->connection->table($this->connection->raw('(' . $myQuery->toSql() . ') count_row_table'))
                                ->setBindings($myQuery->getBindings())->count();
    }

    /**
     * @inheritdoc
     */
    public function filtering()
    {
        $eagerLoads = $this->getEagerLoads();

        $this->query->where(
            function ($query) use ($eagerLoads) {
                $keyword = $this->setupKeyword($this->request->keyword());
                foreach ($this->request->searchableColumnIndex() as $index) {
                    $columnName = $this->setupColumnName($index);

                    if (isset($this->columnDef['filter'][$columnName])) {
                        $method     = Helper::getOrMethod($this->columnDef['filter'][$columnName]['method']);
                        $parameters = $this->columnDef['filter'][$columnName]['parameters'];
                        $this->compileColumnQuery(
                            $this->getQueryBuilder($query), $method, $parameters, $columnName, $keyword
                        );
                    } else {
                        if (count(explode('.', $columnName)) > 1) {
                            $parts      = explode('.', $columnName);
                            $relation   = $parts[0];
                            if (in_array($relation, $eagerLoads)) {
                                $columnName = array_pop($parts);
                                $this->compileRelationSearch($this->getQueryBuilder($query), $relation, $columnName,
                                    $keyword);
                            } else {
                                $this->compileGlobalSearch($this->getQueryBuilder($query), $columnName, $keyword);
                            }
                        } else {
                            $this->compileGlobalSearch($this->getQueryBuilder($query), $columnName, $keyword);
                        }
                    }

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

    /**
     * Get eager loads keys if eloquent.
     *
     * @return array
     */
    private function getEagerLoads()
    {
        if ($this->query_type == 'eloquent') {
            return array_keys($this->query->getEagerLoads());
        }

        return [];
    }

    /**
     * Perform filter column on selected field.
     *
     * @param mixed $query
     * @param string $method
     * @param mixed $parameters
     * @param string $column
     * @param string $keyword
     */
    protected function compileColumnQuery($query, $method, $parameters, $column, $keyword)
    {
        if (method_exists($query, $method)
            && count($parameters) <= with(new \ReflectionMethod($query, $method))->getNumberOfParameters()
        ) {
            if (Str::contains(Str::lower($method), 'raw')
                || Str::contains(Str::lower($method), 'exists')
            ) {
                call_user_func_array(
                    [$query, $method],
                    $this->parameterize($parameters, $keyword)
                );
            } else {
                call_user_func_array(
                    [$query, $method],
                    $this->parameterize($column, $parameters, $keyword)
                );
            }
        }
    }

    /**
     * Build Query Builder Parameters.
     *
     * @return array
     */
    protected function parameterize()
    {
        $args       = func_get_args();
        $keyword    = count($args) > 2 ? $args[2] : $args[1];
        $parameters = Helper::buildParameters($args);
        $parameters = Helper::replacePatternWithKeyword($parameters, $keyword, '$1');

        return $parameters;
    }

    /**
     * Add relation query on global search.
     *
     * @param mixed $query
     * @param string $column
     * @param string $keyword
     */
    protected function compileRelationSearch($query, $relation, $column, $keyword)
    {
        $myQuery = clone $this->query;
        $myQuery->orWhereHas($relation, function ($q) use ($column, $keyword, $query) {
            $q->where($column, 'like', $keyword);
            $sql = $q->toSql();
            $sql = "($sql) >= 1";
            $query->orWhereRaw($sql, [$keyword]);
        });
    }

    /**
     * Add a query on global search.
     *
     * @param mixed $query
     * @param string $column
     * @param string $keyword
     */
    protected function compileGlobalSearch($query, $column, $keyword)
    {
        $column = $this->castColumn($column);
        $sql    = $column . ' LIKE ?';
        if ($this->isCaseInsensitive()) {
            $sql     = 'LOWER(' . $column . ') LIKE ?';
            $keyword = Str::lower($keyword);
        }

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

    /**
     * Wrap a column and cast in pgsql.
     *
     * @param  string $column
     * @return string
     */
    public function castColumn($column)
    {
        $column = $this->connection->getQueryGrammar()->wrap($column);
        if ($this->database === 'pgsql') {
            $column = 'CAST(' . $column . ' as TEXT)';
        }

        return $column;
    }

    /**
     * @inheritdoc
     */
    public function columnSearch()
    {
        $columns = $this->request->get('columns');
        for ($i = 0, $c = count($columns); $i < $c; $i++) {
            if ($this->request->isColumnSearchable($i)) {
                $column  = $this->setupColumnName($i);
                $keyword = $this->getSearchKeyword($i);

                if (isset($this->columnDef['filter'][$column])) {
                    $method     = $this->columnDef['filter'][$column]['method'];
                    $parameters = $this->columnDef['filter'][$column]['parameters'];
                    $this->compileColumnQuery($this->getQueryBuilder(), $method, $parameters, $column, $keyword);
                } else {
                    $column = $this->castColumn($column);
                    if ($this->isCaseInsensitive()) {
                        if ($this->request->isRegex($i)) {
                            $this->query->whereRaw('LOWER(' . $column . ') REGEXP ?', [Str::lower($keyword)]);
                        } else {
                            $this->query->whereRaw('LOWER(' . $column . ') LIKE ?', [Str::lower($keyword)]);
                        }
                    } else {
                        $col = strstr($column, '(') ? $this->connection->raw($column) : $column;
                        if ($this->request->isRegex($i)) {
                            $this->query->whereRaw($col . ' REGEXP ?', [$keyword]);
                        } else {
                            $this->query->whereRaw($col . ' LIKE ?', [$keyword]);
                        }
                    }
                }

                $this->isFilterApplied = true;
            }
        }
    }

    /**
     * Get proper keyword to use for search.
     *
     * @param int $i
     * @return string
     */
    private function getSearchKeyword($i)
    {
        if ($this->request->isRegex($i)) {
            return $this->request->columnKeyword($i);
        }

        return $this->setupKeyword($this->request->columnKeyword($i));
    }

    /**
     * @inheritdoc
     */
    public function ordering()
    {
        if ($this->orderCallback) {
            call_user_func($this->orderCallback, $this->getQueryBuilder());

            return;
        }

        foreach ($this->request->orderableColumns() as $orderable) {
            $column = $this->setupOrderColumn($orderable);
            if (isset($this->columnDef['order'][$column])) {
                $method     = $this->columnDef['order'][$column]['method'];
                $parameters = $this->columnDef['order'][$column]['parameters'];
                $this->compileColumnQuery(
                    $this->getQueryBuilder(), $method, $parameters, $column, $orderable['direction']
                );
            } else {
                /**
                 * If we perform a select("*"), the ORDER BY clause will look like this:
                 * ORDER BY * ASC
                 * which causes a query exception
                 * The temporary fix is modify `*` column to `id` column
                 */
                if ($column === '*') {
                    $column = 'id';
                }
                $this->getQueryBuilder()->orderBy($column, $orderable['direction']);
            }
        }
    }

    /**
     * Get order by column name.
     *
     * @param array $orderable
     * @return string
     */
    private function setupOrderColumn(array $orderable)
    {
        $r_column = $this->request->input('columns')[$orderable['column']];
        $column   = isset($r_column['name']) ? $r_column['name'] : $r_column['data'];
        if ($column >= 0) {
            $column = $this->setupColumnName($orderable['column'], true);

            return $column;
        }

        return $column;
    }

    /**
     * @inheritdoc
     */
    public function paging()
    {
        $this->query->skip($this->request['start'])
                    ->take((int) $this->request['length'] > 0 ? $this->request['length'] : 10);
    }

    /**
     * Get results
     *
     * @return array|static[]
     */
    public function results()
    {
        return $this->query->get();
    }
}


1			<?php
2
3			namespace Yajra\Datatables\Engines;
4
5			/**
6			* Laravel Datatables Query Builder Engine
7			*
8			* @package Laravel
9			* @category Package
10			* @author Arjay Angeles <[email protected]>
11			*/
12
13			use Closure;
14			use Illuminate\Database\Query\Builder;
15			use Illuminate\Support\Str;
16			use Yajra\Datatables\Contracts\DataTableEngineContract;
17			use Yajra\Datatables\Helper;
18			use Yajra\Datatables\Request;
19
20			class QueryBuilderEngine extends BaseEngine implements DataTableEngineContract
21			{
22			/**
23			* @param \Illuminate\Database\Query\Builder $builder
24			* @param \Yajra\Datatables\Request $request
25			*/
26			public function __construct(Builder $builder, Request $request)
27			{
28			$this->query = $builder;
29			$this->init($request, $builder);
30			}
31
32			/**
33			* Initialize attributes.
34			*
35			* @param \Yajra\Datatables\Request $request
36			* @param \Illuminate\Database\Query\Builder $builder
37			* @param string $type
38			*/
39			protected function init($request, $builder, $type = 'builder')
			0 ignored issues – show Bug introduced 2015-12-18 06:23 UTC by Report Bug Copy Issue Report You have injected the Request via parameter `$request`. This is generally not recommended as there might be multiple instances during a request cycle (f.e. when using sub-requests). Instead, it is recommended to inject the RequestStack and retrieve the current request each time you need it via `getCurrentRequest()`. Loading history...
40			{
41			$this->request = $request;
42			$this->query_type = $type;
43			$this->columns = $builder->columns;
44			$this->connection = $builder->getConnection();
45			$this->prefix = $this->connection->getTablePrefix();
46			$this->database = $this->connection->getDriverName();
47			if ($this->isDebugging()) {
48			$this->connection->enableQueryLog();
49			}
50			}
51
52			/**
53			* @inheritdoc
54			*/
55			public function filter(Closure $callback)
56			{
57			$this->overrideGlobalSearch($callback, $this->query);
58
59			return $this;
60			}
61
62			/**
63			* @inheritdoc
64			*/
65			public function make($mDataSupport = false, $orderFirst = false)
66			{
67			return parent::make($mDataSupport, $orderFirst);
68			}
69
70			/**
71			* @inheritdoc
72			*/
73			public function totalCount()
74			{
75			return $this->count();
76			}
77
78			/**
79			* Counts current query.
80			*
81			* @return int
82			*/
83			public function count()
84			{
85			$myQuery = clone $this->query;
86			// if its a normal query ( no union, having and distinct word )
87			// replace the select with static text to improve performance
88			if (! Str::contains(Str::lower($myQuery->toSql()), ['union', 'having', 'distinct', 'order by', 'group by'])) {
89			$row_count = $this->connection->getQueryGrammar()->wrap('row_count');
90			$myQuery->select($this->connection->raw("'1' as {$row_count}"));
91			}
92
93			return $this->connection->table($this->connection->raw('(' . $myQuery->toSql() . ') count_row_table'))
94			->setBindings($myQuery->getBindings())->count();
95			}
96
97			/**
98			* @inheritdoc
99			*/
100			public function filtering()
101			{
102			$eagerLoads = $this->getEagerLoads();
103
104			$this->query->where(
105			function ($query) use ($eagerLoads) {
106			$keyword = $this->setupKeyword($this->request->keyword());
107			foreach ($this->request->searchableColumnIndex() as $index) {
108			$columnName = $this->setupColumnName($index);
109
110			if (isset($this->columnDef['filter'][$columnName])) {
111			$method = Helper::getOrMethod($this->columnDef['filter'][$columnName]['method']);
112			$parameters = $this->columnDef['filter'][$columnName]['parameters'];
113			$this->compileColumnQuery(
114			$this->getQueryBuilder($query), $method, $parameters, $columnName, $keyword
115			);
116			} else {
117			if (count(explode('.', $columnName)) > 1) {
118			$parts = explode('.', $columnName);
119			$relation = $parts[0];
120			if (in_array($relation, $eagerLoads)) {
121			$columnName = array_pop($parts);
122			$this->compileRelationSearch($this->getQueryBuilder($query), $relation, $columnName,
123			$keyword);
124			} else {
125			$this->compileGlobalSearch($this->getQueryBuilder($query), $columnName, $keyword);
126			}
127			} else {
128			$this->compileGlobalSearch($this->getQueryBuilder($query), $columnName, $keyword);
129			}
130			}
131
132			$this->isFilterApplied = true;
133			}
134			}
135			);
136			}
137
138			/**
139			* Get eager loads keys if eloquent.
140			*
141			* @return array
142			*/
143			private function getEagerLoads()
144			{
145			if ($this->query_type == 'eloquent') {
146			return array_keys($this->query->getEagerLoads());
147			}
148
149			return [];
150			}
151
152			/**
153			* Perform filter column on selected field.
154			*
155			* @param mixed $query
156			* @param string $method
157			* @param mixed $parameters
158			* @param string $column
159			* @param string $keyword
160			*/
161			protected function compileColumnQuery($query, $method, $parameters, $column, $keyword)
162			{
163			if (method_exists($query, $method)
164			&& count($parameters) <= with(new \ReflectionMethod($query, $method))->getNumberOfParameters()
165			) {
166			if (Str::contains(Str::lower($method), 'raw')
167			\|\| Str::contains(Str::lower($method), 'exists')
168			) {
169			call_user_func_array(
170			[$query, $method],
171			$this->parameterize($parameters, $keyword)
172			);
173			} else {
174			call_user_func_array(
175			[$query, $method],
176			$this->parameterize($column, $parameters, $keyword)
177			);
178			}
179			}
180			}
181
182			/**
183			* Build Query Builder Parameters.
184			*
185			* @return array
186			*/
187			protected function parameterize()
188			{
189			$args = func_get_args();
190			$keyword = count($args) > 2 ? $args[2] : $args[1];
191			$parameters = Helper::buildParameters($args);
192			$parameters = Helper::replacePatternWithKeyword($parameters, $keyword, '$1');
193
194			return $parameters;
195			}
196
197			/**
198			* Add relation query on global search.
199			*
200			* @param mixed $query
201			* @param string $column
202			* @param string $keyword
203			*/
204			protected function compileRelationSearch($query, $relation, $column, $keyword)
205			{
206			$myQuery = clone $this->query;
207			$myQuery->orWhereHas($relation, function ($q) use ($column, $keyword, $query) {
208			$q->where($column, 'like', $keyword);
209			$sql = $q->toSql();
210			$sql = "($sql) >= 1";
211			$query->orWhereRaw($sql, [$keyword]);
212			});
213			}
214
215			/**
216			* Add a query on global search.
217			*
218			* @param mixed $query
219			* @param string $column
220			* @param string $keyword
221			*/
222			protected function compileGlobalSearch($query, $column, $keyword)
223			{
224			$column = $this->castColumn($column);
225			$sql = $column . ' LIKE ?';
226			if ($this->isCaseInsensitive()) {
227			$sql = 'LOWER(' . $column . ') LIKE ?';
228			$keyword = Str::lower($keyword);
229			}
230
231			$query->orWhereRaw($sql, [$keyword]);
232			}
233
234			/**
235			* Wrap a column and cast in pgsql.
236			*
237			* @param string $column
238			* @return string
239			*/
240			public function castColumn($column)
241			{
242			$column = $this->connection->getQueryGrammar()->wrap($column);
243			if ($this->database === 'pgsql') {
244			$column = 'CAST(' . $column . ' as TEXT)';
245			}
246
247			return $column;
248			}
249
250			/**
251			* @inheritdoc
252			*/
253			public function columnSearch()
254			{
255			$columns = $this->request->get('columns');
256			for ($i = 0, $c = count($columns); $i < $c; $i++) {
257			if ($this->request->isColumnSearchable($i)) {
258			$column = $this->setupColumnName($i);
259			$keyword = $this->getSearchKeyword($i);
260
261			if (isset($this->columnDef['filter'][$column])) {
262			$method = $this->columnDef['filter'][$column]['method'];
263			$parameters = $this->columnDef['filter'][$column]['parameters'];
264			$this->compileColumnQuery($this->getQueryBuilder(), $method, $parameters, $column, $keyword);
265			} else {
266			$column = $this->castColumn($column);
267			if ($this->isCaseInsensitive()) {
268			if ($this->request->isRegex($i)) {
269			$this->query->whereRaw('LOWER(' . $column . ') REGEXP ?', [Str::lower($keyword)]);
270			} else {
271			$this->query->whereRaw('LOWER(' . $column . ') LIKE ?', [Str::lower($keyword)]);
272			}
273			} else {
274			$col = strstr($column, '(') ? $this->connection->raw($column) : $column;
275			if ($this->request->isRegex($i)) {
276			$this->query->whereRaw($col . ' REGEXP ?', [$keyword]);
277			} else {
278			$this->query->whereRaw($col . ' LIKE ?', [$keyword]);
279			}
280			}
281			}
282
283			$this->isFilterApplied = true;
284			}
285			}
286			}
287
288			/**
289			* Get proper keyword to use for search.
290			*
291			* @param int $i
292			* @return string
293			*/
294			private function getSearchKeyword($i)
295			{
296			if ($this->request->isRegex($i)) {
297			return $this->request->columnKeyword($i);
298			}
299
300			return $this->setupKeyword($this->request->columnKeyword($i));
301			}
302
303			/**
304			* @inheritdoc
305			*/
306			public function ordering()
307			{
308			if ($this->orderCallback) {
309			call_user_func($this->orderCallback, $this->getQueryBuilder());
310
311			return;
312			}
313
314			foreach ($this->request->orderableColumns() as $orderable) {
315			$column = $this->setupOrderColumn($orderable);
316			if (isset($this->columnDef['order'][$column])) {
317			$method = $this->columnDef['order'][$column]['method'];
318			$parameters = $this->columnDef['order'][$column]['parameters'];
319			$this->compileColumnQuery(
320			$this->getQueryBuilder(), $method, $parameters, $column, $orderable['direction']
321			);
322			} else {
323			/**
324			* If we perform a select("*"), the ORDER BY clause will look like this:
325			* ORDER BY * ASC
326			* which causes a query exception
327			* The temporary fix is modify `*` column to `id` column
328			*/
329			if ($column === '*') {
330			$column = 'id';
331			}
332			$this->getQueryBuilder()->orderBy($column, $orderable['direction']);
333			}
334			}
335			}
336
337			/**
338			* Get order by column name.
339			*
340			* @param array $orderable
341			* @return string
342			*/
343			private function setupOrderColumn(array $orderable)
344			{
345			$r_column = $this->request->input('columns')[$orderable['column']];
346			$column = isset($r_column['name']) ? $r_column['name'] : $r_column['data'];
347			if ($column >= 0) {
348			$column = $this->setupColumnName($orderable['column'], true);
349
350			return $column;
351			}
352
353			return $column;
354			}
355
356			/**
357			* @inheritdoc
358			*/
359			public function paging()
360			{
361			$this->query->skip($this->request['start'])
362			->take((int) $this->request['length'] > 0 ? $this->request['length'] : 10);
363			}
364
365			/**
366			* Get results
367			*
368			* @return array\|static[]
369			*/
370			public function results()
371			{
372			return $this->query->get();
373			}
374			}
375

yajra / laravel-datatables

Push — master ( 129599...13da9b )

QueryBuilderEngine::filtering() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like