redati · May 9, 2024 00:29 · Apr 16, 2023 · Apr 16, 2023 · Apr 16, 2023 · Apr 16, 2023
diff --git a/SearchBuilder.php b/SearchBuilder.php
@@ -77,7 +77,7 @@ public function build()
                 if ($logicValid && isset($searchBuilder['criteria'])) {
                     foreach ($searchBuilder['criteria'] as $rule) {
                         $col        = $rule['origData'] ?? null;
-                        $searchTerm = (!in_array($rule['condition'], ['null', '!null'])) ? $rule['value1'] ?? false : true;
+                        $searchTerm = (!in_array($rule['condition'] ?? null, ['null', '!null'])) ? $rule['value1'] ?? false : true;
                         if ($col && $searchTerm && array_key_exists($rule['condition'] ?? null, $this->sbRules) && in_array($col, $this->allowedColumns)) {
                             if ($rule['condition'] === 'starts' || $rule['condition'] === '!starts') {
                                 $searchTerm = $searchTerm . '%';

diff --git a/SearchBuilder.php b/SearchBuilder.php
@@ -6,7 +6,7 @@
 
 /**
  * Depth 1 SearchBuilder for DataTable
- * Supports Query Builder and Eloquesnt ORM
+ * Supports Query Builder and Eloquent ORM
  * To prevent unauthorized access to the database, the code uses two security measures.
  * First, it filters out any columns that are not in the $allowedColumns array, which specifies
  * the columns that can be read. Second, it checks if the 'condition' parameter matches any of the

diff --git a/SearchBuilder.php b/SearchBuilder.php
@@ -12,13 +12,15 @@
  * the columns that can be read. Second, it checks if the 'condition' parameter matches any of the
  * rules in the $sbRules list, which defines the valid conditions for the operation.
  *
- * Join query might have issue as Datatable SearchBuilder does not provides table name
+ * If $mapColumns is provided, the join query will function correctly.
  *
  * Example
+ * $allowedColumns = ['name','email','mobile','role'];
+ * $mapColumns = ['name'=>'users.name', 'mobile'=> 'users.mobile', 'email'=> 'users.email', 'role'=>"roles.text"]; // for join query
  * datatables()
  *     ->of($query->select($_columns))
- *     ->filter(function ($query) use ($request, $allowedColumns) {
- *         $sb    = new SearchBuilder($request, $query, $allowedColumns);
+ *     ->filter(function ($query) use ($request, $allowedColumns, $mapColumns) {
+ *         $sb    = new SearchBuilder($request, $query, $allowedColumns, $mapColumns);
  *         $query = $sb->build();
  *     })
  *     ->toJson();
@@ -29,6 +31,7 @@ class SearchBuilder
     protected $request;
     protected $query;
     protected $allowedColumns;
+    protected $mapColumns;
 
     protected $sbRules = [
         '='         => '=',
@@ -49,11 +52,12 @@ class SearchBuilder
         '!between'  => 'not between',
     ];
 
-    public function __construct(Request $request, $query, array $allowedColumns)
+    public function __construct(Request $request, $query, array $allowedColumns, array $mapColumns = [])
     {
         $this->request        = $request;
         $this->query          = $query;
         $this->allowedColumns = $allowedColumns;
+        $this->mapColumns     = $mapColumns;
     }
 
     /**
@@ -73,7 +77,7 @@ public function build()
                 if ($logicValid && isset($searchBuilder['criteria'])) {
                     foreach ($searchBuilder['criteria'] as $rule) {
                         $col        = $rule['origData'] ?? null;
-                        $searchTerm = $rule['value1'] ?? false;
+                        $searchTerm = (!in_array($rule['condition'], ['null', '!null'])) ? $rule['value1'] ?? false : true;
                         if ($col && $searchTerm && array_key_exists($rule['condition'] ?? null, $this->sbRules) && in_array($col, $this->allowedColumns)) {
                             if ($rule['condition'] === 'starts' || $rule['condition'] === '!starts') {
                                 $searchTerm = $searchTerm . '%';
@@ -89,9 +93,11 @@ public function build()
                                     $searchTerm = [$rule['value1'], $rule['value2'] ?? null];
                                 }
                             }
+                            $col       = (!empty($this->mapColumns)) ? $this->mapColumns[$col] ?? $col : $col;
                             $sbLogic[] = [$col, $this->sbRules[$rule['condition'] ?? null], $searchTerm];
                         }
                     }
+
                     if ($sbLogic) {
                         $this->query = $this->query->where(function ($query) use ($sbLogic, $logic) {
                             foreach ($sbLogic as $r) {
@@ -102,6 +108,12 @@ public function build()
                                 } elseif ($r[1] == 'not between') {
                                     $cond = ($logic == 'AND') ? 'whereNotBetween' : 'orWhereNotBetween';
                                     $query->{$cond}($r[0], $r[2]);
+                                } elseif ($r[1] == 'IS NULL') {
+                                    $cond = ($logic == 'AND') ? 'whereNull' : 'orWhereNull';
+                                    $query->{$cond}($r[0]);
+                                } elseif ($r[1] == 'IS NOT NULL') {
+                                    $cond = ($logic == 'AND') ? 'whereNotNull' : 'orWhereNotNull';
+                                    $query->{$cond}($r[0]);
                                 } else {
                                     if ($logic == 'AND') {
                                         $query->where($r[0], $r[1], $r[2]);

diff --git a/SearchBuilder.php b/SearchBuilder.php
@@ -72,9 +72,9 @@ public function build()
                 $logicValid = in_array($logic, ['AND', "OR"]);
                 if ($logicValid && isset($searchBuilder['criteria'])) {
                     foreach ($searchBuilder['criteria'] as $rule) {
-                        $col = $rule['origData'] ?? null;
-                        if ($col && array_key_exists($rule['condition'] ?? null, $this->sbRules) && in_array($col, $this->allowedColumns)) {
-                            $searchTerm = $rule['value1'];
+                        $col        = $rule['origData'] ?? null;
+                        $searchTerm = $rule['value1'] ?? false;
+                        if ($col && $searchTerm && array_key_exists($rule['condition'] ?? null, $this->sbRules) && in_array($col, $this->allowedColumns)) {
                             if ($rule['condition'] === 'starts' || $rule['condition'] === '!starts') {
                                 $searchTerm = $searchTerm . '%';
                             } elseif ($rule['condition'] === 'ends' || $rule['condition'] === '!ends') {

diff --git a/SearchBuilder.php b/SearchBuilder.php
@@ -0,0 +1,122 @@
+<?php
+
+namespace App;
+
+use Illuminate\Http\Request;
+
+/**
+ * Depth 1 SearchBuilder for DataTable
+ * Supports Query Builder and Eloquesnt ORM
+ * To prevent unauthorized access to the database, the code uses two security measures.
+ * First, it filters out any columns that are not in the $allowedColumns array, which specifies
+ * the columns that can be read. Second, it checks if the 'condition' parameter matches any of the
+ * rules in the $sbRules list, which defines the valid conditions for the operation.
+ *
+ * Join query might have issue as Datatable SearchBuilder does not provides table name
+ *
+ * Example
+ * datatables()
+ *     ->of($query->select($_columns))
+ *     ->filter(function ($query) use ($request, $allowedColumns) {
+ *         $sb    = new SearchBuilder($request, $query, $allowedColumns);
+ *         $query = $sb->build();
+ *     })
+ *     ->toJson();
+ */
+
+class SearchBuilder
+{
+    protected $request;
+    protected $query;
+    protected $allowedColumns;
+
+    protected $sbRules = [
+        '='         => '=',
+        '>'         => '>',
+        '>='        => '>=',
+        '<'         => '<',
+        '<='        => '<=',
+        '!='        => '!=',
+        'starts'    => 'LIKE',
+        '!starts'   => 'NOT LIKE',
+        'contains'  => 'LIKE',
+        '!contains' => 'NOT LIKE',
+        'ends'      => 'LIKE',
+        '!ends'     => 'NOT LIKE',
+        'null'      => 'IS NULL',
+        '!null'     => 'IS NOT NULL',
+        'between'   => 'between',
+        '!between'  => 'not between',
+    ];
+
+    public function __construct(Request $request, $query, array $allowedColumns)
+    {
+        $this->request        = $request;
+        $this->query          = $query;
+        $this->allowedColumns = $allowedColumns;
+    }
+
+    /**
+     * Build Query Where Applicable
+     * @note The code is safe from SQL injection attacks, as QueryBuilder and EloquentORM handle the escaping of user input.
+     * @note Unwanted columss are protected by $allowedColumns and 'condition' is protected by $sbRules check list
+     * @return $query
+     */
+    public function build()
+    {
+        if ($this->request->has('searchBuilder')) {
+            $searchBuilder = $this->request->searchBuilder;
+            if ($searchBuilder) {
+                $sbLogic    = [];
+                $logic      = $searchBuilder['logic'] ?? "AND";
+                $logicValid = in_array($logic, ['AND', "OR"]);
+                if ($logicValid && isset($searchBuilder['criteria'])) {
+                    foreach ($searchBuilder['criteria'] as $rule) {
+                        $col = $rule['origData'] ?? null;
+                        if ($col && array_key_exists($rule['condition'] ?? null, $this->sbRules) && in_array($col, $this->allowedColumns)) {
+                            $searchTerm = $rule['value1'];
+                            if ($rule['condition'] === 'starts' || $rule['condition'] === '!starts') {
+                                $searchTerm = $searchTerm . '%';
+                            } elseif ($rule['condition'] === 'ends' || $rule['condition'] === '!ends') {
+                                $searchTerm = '%' . $searchTerm;
+                            } elseif ($rule['condition'] === 'contains' || $rule['condition'] === '!contains') {
+                                $searchTerm = '%' . $searchTerm . '%';
+                            } elseif ($rule['condition'] === 'between' || $rule['condition'] === '!between') {
+                                if (preg_match("/^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$/", $rule['value1'])) {
+                                    $date2      = $rule['value2'] ?? $rule['value1'];
+                                    $searchTerm = [$rule['value1'] . " 00:00:00", $date2 . " 23:59:59"];
+                                } else {
+                                    $searchTerm = [$rule['value1'], $rule['value2'] ?? null];
+                                }
+                            }
+                            $sbLogic[] = [$col, $this->sbRules[$rule['condition'] ?? null], $searchTerm];
+                        }
+                    }
+                    if ($sbLogic) {
+                        $this->query = $this->query->where(function ($query) use ($sbLogic, $logic) {
+                            foreach ($sbLogic as $r) {
+                                $cond = 'where';
+                                if ($r[1] == 'between') {
+                                    $cond = ($logic == 'AND') ? 'whereBetween' : 'orWhereBetween';
+                                    $query->{$cond}($r[0], $r[2]);
+                                } elseif ($r[1] == 'not between') {
+                                    $cond = ($logic == 'AND') ? 'whereNotBetween' : 'orWhereNotBetween';
+                                    $query->{$cond}($r[0], $r[2]);
+                                } else {
+                                    if ($logic == 'AND') {
+                                        $query->where($r[0], $r[1], $r[2]);
+                                    } else {
+                                        $query->orWhere($r[0], $r[1], $r[2]);
+                                    }
+                                }
+                            }
+                        });
+                    }
+                }
+            }
+        }
+
+        return $this->query;
+    }
+
+}
No results found