1: <?php
  2: 
  3: namespace Yajra\Datatables\Engines;
  4: 
  5: use Closure;
  6: use Illuminate\Database\Eloquent\Relations\BelongsToMany;
  7: use Illuminate\Database\Eloquent\Relations\HasOneOrMany;
  8: use Illuminate\Database\Eloquent\Relations\MorphToMany;
  9: use Illuminate\Database\Query\Builder;
 10: use Illuminate\Support\Str;
 11: use Yajra\Datatables\Helper;
 12: use Yajra\Datatables\Request;
 13: 
 14: /**
 15:  * Class QueryBuilderEngine.
 16:  *
 17:  * @package Yajra\Datatables\Engines
 18:  * @author  Arjay Angeles <aqangeles@gmail.com>
 19:  */
 20: class QueryBuilderEngine extends BaseEngine
 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')
 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:      * Set auto filter off and run your own filter.
 54:      * Overrides global search
 55:      *
 56:      * @param \Closure $callback
 57:      * @param bool $globalSearch
 58:      * @return $this
 59:      */
 60:     public function filter(Closure $callback, $globalSearch = false)
 61:     {
 62:         $this->overrideGlobalSearch($callback, $this->query, $globalSearch);
 63: 
 64:         return $this;
 65:     }
 66: 
 67:     /**
 68:      * Organizes works
 69:      *
 70:      * @param bool $mDataSupport
 71:      * @param bool $orderFirst
 72:      * @return \Illuminate\Http\JsonResponse
 73:      */
 74:     public function make($mDataSupport = false, $orderFirst = false)
 75:     {
 76:         return parent::make($mDataSupport, $orderFirst);
 77:     }
 78: 
 79:     /**
 80:      * Count total items.
 81:      *
 82:      * @return integer
 83:      */
 84:     public function totalCount()
 85:     {
 86:         return $this->totalRecords ? $this->totalRecords : $this->count();
 87:     }
 88: 
 89:     /**
 90:      * Counts current query.
 91:      *
 92:      * @return int
 93:      */
 94:     public function count()
 95:     {
 96:         $myQuery = clone $this->query;
 97:         // if its a normal query ( no union, having and distinct word )
 98:         // replace the select with static text to improve performance
 99:         if (! Str::contains(Str::lower($myQuery->toSql()), ['union', 'having', 'distinct', 'order by', 'group by'])) {
100:             $row_count = $this->wrap('row_count');
101:             $myQuery->select($this->connection->raw("'1' as {$row_count}"));
102:         }
103: 
104:         // check for select soft deleted records
105:         if (! $this->withTrashed && $this->modelUseSoftDeletes()) {
106:             $myQuery->whereNull($myQuery->getModel()->getTable() . '.deleted_at');
107:         }
108: 
109:         return $this->connection->table($this->connection->raw('(' . $myQuery->toSql() . ') count_row_table'))
110:                                 ->setBindings($myQuery->getBindings())->count();
111:     }
112: 
113:     /**
114:      * Wrap column with DB grammar.
115:      *
116:      * @param string $column
117:      * @return string
118:      */
119:     protected function wrap($column)
120:     {
121:         return $this->connection->getQueryGrammar()->wrap($column);
122:     }
123: 
124:     /**
125:      * Check if model use SoftDeletes trait
126:      *
127:      * @return boolean
128:      */
129:     private function modelUseSoftDeletes()
130:     {
131:         if ($this->query_type == 'eloquent') {
132:             return in_array('Illuminate\Database\Eloquent\SoftDeletes', class_uses($this->query->getModel()));
133:         }
134: 
135:         return false;
136:     }
137: 
138:     /**
139:      * Perform global search.
140:      *
141:      * @return void
142:      */
143:     public function filtering()
144:     {
145:         $this->query->where(
146:             function ($query) {
147:                 $globalKeyword = $this->request->keyword();
148:                 $queryBuilder  = $this->getQueryBuilder($query);
149: 
150:                 foreach ($this->request->searchableColumnIndex() as $index) {
151:                     $columnName = $this->getColumnName($index);
152:                     if ($this->isBlacklisted($columnName)) {
153:                         continue;
154:                     }
155: 
156:                     // check if custom column filtering is applied
157:                     if (isset($this->columnDef['filter'][$columnName])) {
158:                         $columnDef = $this->columnDef['filter'][$columnName];
159:                         // check if global search should be applied for the specific column
160:                         $applyGlobalSearch = count($columnDef['parameters']) == 0 || end($columnDef['parameters']) !== false;
161:                         if (! $applyGlobalSearch) {
162:                             continue;
163:                         }
164: 
165:                         if ($columnDef['method'] instanceof Closure) {
166:                             $whereQuery = $queryBuilder->newQuery();
167:                             call_user_func_array($columnDef['method'], [$whereQuery, $globalKeyword]);
168:                             $queryBuilder->addNestedWhereQuery($whereQuery, 'or');
169:                         } else {
170:                             $this->compileColumnQuery(
171:                                 $queryBuilder,
172:                                 Helper::getOrMethod($columnDef['method']),
173:                                 $columnDef['parameters'],
174:                                 $columnName,
175:                                 $globalKeyword
176:                             );
177:                         }
178:                     } else {
179:                         if (count(explode('.', $columnName)) > 1) {
180:                             $eagerLoads     = $this->getEagerLoads();
181:                             $parts          = explode('.', $columnName);
182:                             $relationColumn = array_pop($parts);
183:                             $relation       = implode('.', $parts);
184:                             if (in_array($relation, $eagerLoads)) {
185:                                 $this->compileRelationSearch(
186:                                     $queryBuilder,
187:                                     $relation,
188:                                     $relationColumn,
189:                                     $globalKeyword
190:                                 );
191:                             } else {
192:                                 $this->compileQuerySearch($queryBuilder, $columnName, $globalKeyword);
193:                             }
194:                         } else {
195:                             $this->compileQuerySearch($queryBuilder, $columnName, $globalKeyword);
196:                         }
197:                     }
198: 
199:                     $this->isFilterApplied = true;
200:                 }
201:             }
202:         );
203:     }
204: 
205:     /**
206:      * Perform filter column on selected field.
207:      *
208:      * @param mixed $query
209:      * @param string|Closure $method
210:      * @param mixed $parameters
211:      * @param string $column
212:      * @param string $keyword
213:      */
214:     protected function compileColumnQuery($query, $method, $parameters, $column, $keyword)
215:     {
216:         if (method_exists($query, $method)
217:             && count($parameters) <= with(new \ReflectionMethod($query, $method))->getNumberOfParameters()
218:         ) {
219:             if (Str::contains(Str::lower($method), 'raw')
220:                 || Str::contains(Str::lower($method), 'exists')
221:             ) {
222:                 call_user_func_array(
223:                     [$query, $method],
224:                     $this->parameterize($parameters, $keyword)
225:                 );
226:             } else {
227:                 call_user_func_array(
228:                     [$query, $method],
229:                     $this->parameterize($column, $parameters, $keyword)
230:                 );
231:             }
232:         }
233:     }
234: 
235:     /**
236:      * Build Query Builder Parameters.
237:      *
238:      * @return array
239:      */
240:     protected function parameterize()
241:     {
242:         $args       = func_get_args();
243:         $keyword    = count($args) > 2 ? $args[2] : $args[1];
244:         $parameters = Helper::buildParameters($args);
245:         $parameters = Helper::replacePatternWithKeyword($parameters, $keyword, '$1');
246: 
247:         return $parameters;
248:     }
249: 
250:     /**
251:      * Get eager loads keys if eloquent.
252:      *
253:      * @return array
254:      */
255:     protected function getEagerLoads()
256:     {
257:         if ($this->query_type == 'eloquent') {
258:             return array_keys($this->query->getEagerLoads());
259:         }
260: 
261:         return [];
262:     }
263: 
264:     /**
265:      * Add relation query on global search.
266:      *
267:      * @param mixed $query
268:      * @param string $relation
269:      * @param string $column
270:      * @param string $keyword
271:      */
272:     protected function compileRelationSearch($query, $relation, $column, $keyword)
273:     {
274:         $myQuery      = clone $this->query;
275:         $relationType = $myQuery->getModel()->{$relation}();
276:         $myQuery->orWhereHas($relation, function ($builder) use ($column, $keyword, $query, $relationType) {
277:             $builder->select($this->connection->raw('count(1)'));
278:             $this->compileQuerySearch($builder, $column, $keyword, '');
279:             $builder = "({$builder->toSql()}) >= 1";
280: 
281:             if ($relationType instanceof MorphToMany) {
282:                 $query->orWhereRaw($builder, [$relationType->getMorphClass(), $this->prepareKeyword($keyword)]);
283:             } else {
284:                 $query->orWhereRaw($builder, [$this->prepareKeyword($keyword)]);
285:             }
286:         });
287:     }
288: 
289:     /**
290:      * Compile query builder where clause depending on configurations.
291:      *
292:      * @param mixed $query
293:      * @param string $column
294:      * @param string $keyword
295:      * @param string $relation
296:      */
297:     protected function compileQuerySearch($query, $column, $keyword, $relation = 'or')
298:     {
299:         $column = $this->castColumn($column);
300:         $sql    = $column . ' LIKE ?';
301: 
302:         if ($this->isCaseInsensitive()) {
303:             $sql = 'LOWER(' . $column . ') LIKE ?';
304:         }
305: 
306:         $query->{$relation . 'WhereRaw'}($sql, [$this->prepareKeyword($keyword)]);
307:     }
308: 
309:     /**
310:      * Wrap a column and cast in pgsql.
311:      *
312:      * @param  string $column
313:      * @return string
314:      */
315:     public function castColumn($column)
316:     {
317:         $column = $this->wrap($column);
318:         if ($this->database === 'pgsql') {
319:             $column = 'CAST(' . $column . ' as TEXT)';
320:         } elseif ($this->database === 'firebird') {
321:             $column = 'CAST(' . $column . ' as VARCHAR(255))';
322:         }
323: 
324:         return $column;
325:     }
326: 
327:     /**
328:      * Prepare search keyword based on configurations.
329:      *
330:      * @param string $keyword
331:      * @return string
332:      */
333:     protected function prepareKeyword($keyword)
334:     {
335:         if ($this->isCaseInsensitive()) {
336:             $keyword = Str::lower($keyword);
337:         }
338: 
339:         if ($this->isWildcard()) {
340:             $keyword = $this->wildcardLikeString($keyword);
341:         }
342: 
343:         if ($this->isSmartSearch()) {
344:             $keyword = "%$keyword%";
345:         }
346: 
347:         return $keyword;
348:     }
349: 
350:     /**
351:      * Perform column search.
352:      *
353:      * @return void
354:      */
355:     public function columnSearch()
356:     {
357:         $columns = $this->request->get('columns', []);
358: 
359:         foreach ($columns as $index => $column) {
360:             if (! $this->request->isColumnSearchable($index)) {
361:                 continue;
362:             }
363: 
364:             $column = $this->getColumnName($index);
365: 
366:             if (isset($this->columnDef['filter'][$column])) {
367:                 $columnDef = $this->columnDef['filter'][$column];
368:                 // get a raw keyword (without wildcards)
369:                 $keyword = $this->getSearchKeyword($index, true);
370:                 $builder = $this->getQueryBuilder();
371: 
372:                 if ($columnDef['method'] instanceof Closure) {
373:                     $whereQuery = $builder->newQuery();
374:                     call_user_func_array($columnDef['method'], [$whereQuery, $keyword]);
375:                     $builder->addNestedWhereQuery($whereQuery);
376:                 } else {
377:                     $this->compileColumnQuery(
378:                         $builder,
379:                         $columnDef['method'],
380:                         $columnDef['parameters'],
381:                         $column,
382:                         $keyword
383:                     );
384:                 }
385:             } else {
386:                 if (count(explode('.', $column)) > 1) {
387:                     $eagerLoads     = $this->getEagerLoads();
388:                     $parts          = explode('.', $column);
389:                     $relationColumn = array_pop($parts);
390:                     $relation       = implode('.', $parts);
391:                     if (in_array($relation, $eagerLoads)) {
392:                         $column = $this->joinEagerLoadedColumn($relation, $relationColumn);
393:                     }
394:                 }
395: 
396:                 $keyword = $this->getSearchKeyword($index);
397:                 $this->compileColumnSearch($index, $column, $keyword);
398:             }
399: 
400:             $this->isFilterApplied = true;
401:         }
402:     }
403: 
404:     /**
405:      * Get proper keyword to use for search.
406:      *
407:      * @param int $i
408:      * @param bool $raw
409:      * @return string
410:      */
411:     private function getSearchKeyword($i, $raw = false)
412:     {
413:         $keyword = $this->request->columnKeyword($i);
414:         if ($raw || $this->request->isRegex($i)) {
415:             return $keyword;
416:         }
417: 
418:         return $this->setupKeyword($keyword);
419:     }
420: 
421:     /**
422:      * Join eager loaded relation and get the related column name.
423:      *
424:      * @param string $relation
425:      * @param string $relationColumn
426:      * @return string
427:      */
428:     protected function joinEagerLoadedColumn($relation, $relationColumn)
429:     {
430:         $joins = [];
431:         foreach ((array) $this->getQueryBuilder()->joins as $key => $join) {
432:             $joins[] = $join->table;
433:         }
434: 
435:         $model = $this->query->getRelation($relation);
436:         if ($model instanceof BelongsToMany) {
437:             $pivot   = $model->getTable();
438:             $pivotPK = $model->getForeignKey();
439:             $pivotFK = $model->getQualifiedParentKeyName();
440: 
441:             if (! in_array($pivot, $joins)) {
442:                 $this->getQueryBuilder()->leftJoin($pivot, $pivotPK, '=', $pivotFK);
443:             }
444: 
445:             $related = $model->getRelated();
446:             $table   = $related->getTable();
447:             $tablePK = $related->getForeignKey();
448:             $tableFK = $related->getQualifiedKeyName();
449: 
450:             if (! in_array($table, $joins)) {
451:                 $this->getQueryBuilder()->leftJoin($table, $pivot . '.' . $tablePK, '=', $tableFK);
452:             }
453:         } else {
454:             $table = $model->getRelated()->getTable();
455:             if ($model instanceof HasOneOrMany) {
456:                 $foreign = $model->getForeignKey();
457:                 $other   = $model->getQualifiedParentKeyName();
458:             } else {
459:                 $foreign = $model->getQualifiedForeignKey();
460:                 $other   = $model->getQualifiedOtherKeyName();
461:             }
462: 
463:             if (! in_array($table, $joins)) {
464:                 $this->getQueryBuilder()->leftJoin($table, $foreign, '=', $other);
465:             }
466:         }
467: 
468:         $column = $table . '.' . $relationColumn;
469: 
470:         return $column;
471:     }
472: 
473:     /**
474:      * Compile queries for column search.
475:      *
476:      * @param int $i
477:      * @param mixed $column
478:      * @param string $keyword
479:      */
480:     protected function compileColumnSearch($i, $column, $keyword)
481:     {
482:         if ($this->request->isRegex($i)) {
483:             $column = strstr($column, '(') ? $this->connection->raw($column) : $column;
484:             $this->regexColumnSearch($column, $keyword);
485:         } else {
486:             $this->compileQuerySearch($this->query, $column, $keyword, '');
487:         }
488:     }
489: 
490:     /**
491:      * Compile regex query column search.
492:      *
493:      * @param mixed $column
494:      * @param string $keyword
495:      */
496:     protected function regexColumnSearch($column, $keyword)
497:     {
498:         if ($this->isOracleSql()) {
499:             $sql = ! $this->isCaseInsensitive() ? 'REGEXP_LIKE( ' . $column . ' , ? )' : 'REGEXP_LIKE( LOWER(' . $column . ') , ?, \'i\' )';
500:             $this->query->whereRaw($sql, [$keyword]);
501:         } else {
502:             $sql = ! $this->isCaseInsensitive() ? $column . ' REGEXP ?' : 'LOWER(' . $column . ') REGEXP ?';
503:             $this->query->whereRaw($sql, [Str::lower($keyword)]);
504:         }
505:     }
506: 
507:     /**
508:      * Perform sorting of columns.
509:      *
510:      * @return void
511:      */
512:     public function ordering()
513:     {
514:         if ($this->orderCallback) {
515:             call_user_func($this->orderCallback, $this->getQueryBuilder());
516: 
517:             return;
518:         }
519: 
520:         foreach ($this->request->orderableColumns() as $orderable) {
521:             $column = $this->getColumnName($orderable['column'], true);
522: 
523:             if ($this->isBlacklisted($column)) {
524:                 continue;
525:             }
526: 
527:             if (isset($this->columnDef['order'][$column])) {
528:                 $method     = $this->columnDef['order'][$column]['method'];
529:                 $parameters = $this->columnDef['order'][$column]['parameters'];
530:                 $this->compileColumnQuery(
531:                     $this->getQueryBuilder(),
532:                     $method,
533:                     $parameters,
534:                     $column,
535:                     $orderable['direction']
536:                 );
537:             } else {
538:                 $valid = 1;
539:                 if (count(explode('.', $column)) > 1) {
540:                     $eagerLoads     = $this->getEagerLoads();
541:                     $parts          = explode('.', $column);
542:                     $relationColumn = array_pop($parts);
543:                     $relation       = implode('.', $parts);
544: 
545:                     if (in_array($relation, $eagerLoads)) {
546:                         $relationship = $this->query->getRelation($relation);
547:                         if (! ($relationship instanceof MorphToMany)) {
548:                             $column = $this->joinEagerLoadedColumn($relation, $relationColumn);
549:                         } else {
550:                             $valid = 0;
551:                         }
552:                     }
553:                 }
554: 
555:                 if ($valid == 1) {
556:                     $this->getQueryBuilder()->orderBy($column, $orderable['direction']);
557:                 }
558:             }
559:         }
560:     }
561: 
562:     /**
563:      * Perform pagination
564:      *
565:      * @return void
566:      */
567:     public function paging()
568:     {
569:         $this->query->skip($this->request['start'])
570:                     ->take((int) $this->request['length'] > 0 ? $this->request['length'] : 10);
571:     }
572: 
573:     /**
574:      * Get results
575:      *
576:      * @return array|static[]
577:      */
578:     public function results()
579:     {
580:         return $this->query->get();
581:     }
582: }
583: