Global search with laravel

Question

I wrote a code to search in within a data table whereby entering a id and customer name but i need to search extra field without specifying the column.

I need to search by column, there's the controller with Search function:

public function search(Request $request)
    {

        if ($request->ajax()) {

            $output = "";
            $indents = DB::table('indents')
                ->where('id', 'LIKE', '%' . $request->search . '%')
                ->orwhere('customer_name', 'LIKE', '%' . $request->search . '%')
                ->orwhere('*', 'LIKE', '%'.$request->search. '%')
                ->get();
                $count=1;
            foreach ($indents as $key => $indent) {


                    $output .= '<tr>' .
                    '<td>'.$count++.'</td>'.
//                    '<td>' . $indent->id . '</td>' .
                    '<td>' . $indent->customer_name . '</td>' .
                    '<td>' . $indent->phone_no . '</td>' .
                    '<td>' . $indent->date_of_delivery . '</td>' .
                    '<td>' . $indent->delivery_at . '</td>' .
                    '<td>' . '.<a href="' . route('edp.show', $indent->id) . '">.' . '<img src="assets/images/select.jpg" class="imgsize">.' . '</a>.' . '</td>' .
                    '</tr>';

            }
            return Response($output);
        }

    }

You can check this out https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database — afsal c, Apr 05 '18 at 10:00

score 2 · Accepted Answer · answered Apr 05 '18 at 09:59

You can have an array of columns to search in like this:

$columnsToSearch = ['column1', 'column2', 'column3'];

These columns can be part of the $request if you need it to be passed from the frontend/api.

Then loop through each $columnsToSearch and add to your query builder like so:

$columnsToSearch = ['column1', 'column2', 'column3'];
// or $columnsToSearch = $request->input('columns_to_search');

$searchQuery = '%' . $request->search . '%';

$indents = DB::table('indents')
            ->where('id', 'LIKE', $searchQuery);

foreach($columnsToSearch as $column) {
    $indents = $indents->orWhere($column, 'LIKE', $searchQuery);
}

$indents = $intents->get();

score 1 · Answer 2 · answered Apr 05 '18 at 09:59

1

You can use Laravel Scount Laravel Scout

Or you can implement yourself, using mysql Mysql Full-text Search

answered Apr 05 '18 at 09:59

Vahe Galstyan

1,681
1
12
25

score 1 · Answer 3 · answered Apr 05 '18 at 10:01

1

You can get all table columns with:

$columns = DB::getSchemaBuilder()->getColumnListing('indents');

Then for each column add orwhere() condition. Beware though, the search will be slow for big tables, unless you add indexing.

answered Apr 05 '18 at 10:01

Julius Žaromskis

2,026
2
14
20

Global search with laravel

3 Answers3