My solution to search column from a joined table (DisplayDatatablesAsync)

[s6carlo]

I wrote a custom display to search column from a joined table, it need a custom column and a custom display model

In app/Admin/boostrap.php custom column and registrations

class MultiSearchString extends String
{
    /**
     * @return View
     */
    protected $searchTable='';
    /**
     * Get or set table 
     * @param string|null $searchTable 
     * @return $this|string
     */
    public function searchTable($searchTable = null)
    {
        if (is_null($searchTable))
        {
            return $this->searchTable;
        }
        $this->searchTable = $searchTable;
        return $this;
    }
}
// register the new column
Column::register('multiSearchString', 'MultiSearchString');
// register the new display class
AdminDisplay::register('datatablesAsyncMultiSearch', '\s6c\DatatablesAsyncMultiSearch');

I create a folder inside /app/Admin called s6c with a DatatablesAsyncMultiSearch.php file , it is used to override the applySearch method

<?php
namespace s6c;
use AdminTemplate;
use Carbon\Carbon;
use Input;
use Route;
use SleepingOwl\Admin\ColumnFilters\Date;
use SleepingOwl\Admin\Columns\Column\DateTime;
use SleepingOwl\Admin\Columns\Column\NamedColumn;
use SleepingOwl\Admin\Columns\Column\String;
use SleepingOwl\Admin\Interfaces\WithRoutesInterface;

class DatatablesAsyncMultiSearch extends  \SleepingOwl\Admin\Display\DisplayDatatablesAsync {

     protected function applySearch($query)
    {

        $search = Input::get('search.value');
        if (is_null($search))
        {
            return;
        }
           $query->where(function ($query) use ($search)
        {
            $columns = $this->columns();
            foreach ($columns as $column)
            {
                           if (get_class($column)== 'MultiSearchString')
                {
                    $name = $column->name();
                    $table = $column->searchTable();
                    $query->orWhere($table.'.'.$name, 'like', '%' . $search . '%');

                }
                elseif ($column instanceof String)
                {
                    $name = $column->name();
                    if ($this->repository->hasColumn($name))
                    {
                        $query->orWhere($name, 'like', '%' . $search . '%');
                    }
                }
            }
        });
    }

}

Now in the my model (Exampe.php) I add the method

// main table of this model
 protected $table = 'tableA';
// joined table
    public function newQuery(){
        $query = parent::newQuery();
        $query=$query->where('isEva', '=','1');
        $query=$query->join('tableB', 'tableB.id', '=', 'tableA.key_B');
        return $query;
    }

Finally in app\Example.php

Admin::model('App\Exampe')->title('ExampeA')
    ->display(function ()
    {
       $display = AdminDisplay::datatablesAsyncMultiSearch();
       $display->columns([
             // other string columns...
Column::multiSearchString('columnFromB')->label('B Searchable')->searchTable('tableB'),

Now if you try to search a text the input is used also in the columng from the second table (in the example tableB, thanks to the new applySearch method of the custom display.

My two cents for this project! ( #112, #63, #237 )

[mapeveri]

@s6carlo this is for v3?. This has méthod display that I not use. For the last version, you know how to do?

Thanks!

[s6carlo]

Yes is for v3, because I have to manage a lot of data and datatableasync is the best way, so I exend it. For other versione you should check if the display method you use have a method applysearch to be overridden in a subclass.

[mapeveri]

@s6carlo Could you explain a little more as it would in the others version?. Thanks!

[s6carlo]

@mapeveri You should follow these steps:

• open the code of the display class you use (in my case it is DisplayDatatablesAsync)
• if it has a method "applySearch" this is the one used to perform search operations
• you can extend this display class and write a new applySearch method
• a new custom column has to be used to select the table where the foreign column are

Sorry but I am using the dev version and I don't have the code for v3.

[mapeveri]

@s6carlo thanks!!