Implementing SearchBuilder with Yajra DataTables in Laravel: A Step-by-Step Guide

Hi everyone, in this blog post I’m going to show you how to use SearchBuilder with Yajra DataTable in Laravel. SearchBuilder is a cool extension for DataTable that allows you to create complex search conditions for your data. It’s very useful if you have a lot of data and you want to filter it by different criteria.

First, let’s assume you already have a working Yajra DataTable [https://packagist.org/packages/yajra/laravel-datatables-oracle]. Now, you want to add the ability for your users to filter and search through this data using the SearchBuilder extension. SearchBuilder adds user defined complex search to the DataTable with the capability to search the DataTable by creating conditions.

To get started with SearchBuilder, you need to follow these steps:

– Install SearchBuilder from https://datatables.net/extensions/searchbuilder/examples/. You can use CDN links or download the files and include them in your project.

– Add the SearchBuilder script and CSS files to your view where you have the DataTable. Make sure they are loaded after the DataTable files.

– Initialize the SearchBuilder plugin in your DataTable options. You can use the default options or customize them according to your needs. For example (using Html Builder) :

$html = $builder->columns($columns)
    ->parameters([
        "searchBuilder" => [
            'depthLimit' => 1,
        ],
        'dom' => '<"row"<"col-12 query"Q><"col-md-6"l><"col-md-6"f>>rtip'
    ]);

Now you can see a button on the top left corner of your table that says “Add Condition“. Click on it and you will see a panel where you can create your search conditions. You can add multiple conditions and group them by AND or OR operators. You can also delete or edit any condition.

That’s it! You have successfully integrated SearchBuilder with Yajra DataTable in Laravel. However, there is one problem. Yajra DataTable doesn’t support SearchBuilder by default, so it won’t work with server-side processing. If you want to make it work with server-side processing, you need to do some extra work.

Here is what you need to do:

Create a file named SearchBuilder.php in App folder.

Copy and paste the following code in it:

	<?php

	namespace App;

	use Illuminate\Http\Request;

	/**
	* Depth 1 SearchBuilder for DataTable
	* 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
	* rules in the $sbRules list, which defines the valid conditions for the operation.
	*
	* 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, $mapColumns) {
	* $sb = new SearchBuilder($request, $query, $allowedColumns, $mapColumns);
	* $query = $sb->build();
	* })
	* ->toJson();
	*/

	class SearchBuilder
	{
	protected $request;
	protected $query;
	protected $allowedColumns;
	protected $mapColumns;

	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, array $mapColumns = [])
	{
	$this->request = $request;
	$this->query = $query;
	$this->allowedColumns = $allowedColumns;
	$this->mapColumns = $mapColumns;
	}

	/**
	* 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;
	$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 . '%';
	} 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];
	}
	}
	$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) {
	$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]);
	} 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]);
	} else {
	$query->orWhere($r[0], $r[1], $r[2]);
	}
	}
	}
	});
	}
	}
	}
	}

	return $this->query;
	}

	}

view raw SearchBuilder.php hosted with

by GitHub

In your controller where you handle the DataTable server-side processing, use the SearchBuilder as filter. For example (No Join Query):

use App\SearchBuilder;

return datatables()
    ->of($query->select($_columns))
    ->filter(function ($query) use ($request, $allowedColumns) {
        $sb    = new SearchBuilder($request, $query, $allowedColumns);
        $query = $sb->build();
    })
    ->toJson();

Example (With Join Query):

use App\SearchBuilder;

$allowedColumns = ['name','email','mobile','role'];
$mapColumns = ['name'=>'users.name', 'mobile'=> 'users.mobile', 'email'=> 'users.email', 'role'=>"roles.text"];
return datatables()
    ->of($query->select($_columns))
    ->filter(function ($query) use ($request, $allowedColumns, $mapColumns) {
        $sb    = new SearchBuilder($request, $query, $allowedColumns, $mapColumns);
        $query = $sb->build();
    })
    ->toJson();

In the given code, the $_columns variable contains the names of the columns in the database table, while the $allowedColumns variable specifies the columns where custom search conditions from SearchBuilder can be applied.

You have now completed the necessary steps to implement SearchBuilder with Yajra DataTables in your Laravel project.

Leave a Reply Cancel reply