Tag: Yajra DataTable

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:

1. Create a file named SearchBuilder.php in App folder.
2. Copy and paste the following code in it:
   
3. 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.