CODEWITHSUNDEEP
phpjquerylaravelautocomplete
Marian Pop
Marian Pop

Reputation: 361

Autocomplete Search in Laravel 5.5 with Typeahead.js using two table columns

I have a database looking like this:

-------------------------------------
| id | FirstName | LastName | Email |
-------------------------------------
|  1 |    Test   | Customer | null  |
|  2 |    Some   |   Name   | null  |
-------------------------------------

Now what I want is to get the id of a customer by matching the FirstName and LastName... if those are find on a row then go get the id.

I'm using Laravel 5.5.28 and I tried Typeahead.js in order to achieve what I initially wanted but for some reason I can only make it work with one column either FirstName or LastName.

This is the working code (only for FirstName):

CDN's

  <script src="{{ asset('js/vendor/jquery.min.js') }}"></script>
  <script src="{{ asset('js/vendor/popper.min.js') }}"></script>
  <script src="{{ asset('js/vendor/bootstrap.min.js') }}"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.1/bootstrap3-typeahead.min.js"></script>

SearchController.php 

public function autocomplete(Request $request)
    {
        $datas = Customers::select("FirstName")
                 ->where("FirstName", "LIKE","%{$request->input('query')}%")
                 ->get();

        $dataModified = array();
        foreach ($datas as $data)
        {
            $dataModified[] = $data->FirstName;
        }

        return response()->json($dataModified);
    }
}

Getting the id in "JobsController.php"

public function store(Request $request)
    {
        $job =  new Jobs;
        $job->cust_id = Customers::where('FirstName', $request->input('customer_name'))->value('id');

        dump($job->cust_id);
    }

This is the view:

{!! Form::open(['action' => 'JobsController@store', 'method' => 'POST']) !!}

{{Form::text('customer_name', '', ['class' => 'form-control typeahead', 'placeholder' => 'Type in customer name...'])}}
{{Form::submit('Create Job', ['class' => 'btn btn-primary mt-2'])}}

{!! Form::close() !!}

Routes

Route::get('jobs.create', 'SearchController@index')->name('search');
Route::get('autocomplete', 'SearchController@autocomplete')->name('autocomplete');

jQuery

<script type="text/javascript">
     var path = "{{ route('autocomplete') }}";
       $('input.typeahead').typeahead({
         source: function(query, process) {
         return $.get(path, { query: query }, function(data) {
         return process(data);
        });
           }
            });
</script>

Now this is working just fine but just for the FirstName. What I want is to make it work for the full name (FirstName + LastName)

I managed to make it work with both FirstName and LastName but just on the "front-end"... it's not getting an id from the database.

Here's what I've done in order to have both FirstName and LastName in the autocompletion suggestions (when starting to type):

SearchController.php

public function autocomplete(Request $request)
    {
        $datas = Customers::select("FirstName", "LastName")
                 ->where("FirstName", "LIKE","%{$request->input('query')}%")
                 ->get();

        $dataModified = array();
        foreach ($datas as $data)
        {
            $dataModified[] = $data->FirstName .' '. $data->LastName;
        }

        return response()->json($dataModified);
    }

It is showing both the FirstName and LastName when typing in but it's returning a null "id".

I would like to be able to use both FirstName and LastName columns in my query so I can only get an id when FirstName and LastName are a match.

Looking forward for your thoughts.

Thanks for your time. Really appreciate it.

Update #1

In the meantime I've changed the query in SearchController so I think the code here is ok now:

$datas = Customers::select("FirstName", "LastName")
                    ->where(function($q) use($request) {
                        $q->where("FirstName", "LIKE","%{$request->input('query')}%")
                          ->orWhere("LastName", "LIKE","%{$request->input('query')}%");
                    })->get();

The only problem is that I still get "null" id's.

Could be because the way I'm trying to get the id ?

In JobsController I have the "store" method like this:

public function store(Request $request)
    {
        $job =  new Jobs;
        $job->cust_id = Customers::where('FirstName', $request->input('customer_name'))
                                    ->orWhere('LastName', $request->input('customer_name'))
                                    ->value('id');

    }

Is this the correct way to get the ID ?

Update #2

I was concerned that the $request->input is not returning a correct format hence the reason why I get a 'null' id on my query but I was wrong because a simple dump(); shows that the $request->input is returning the expected string.

I really ran out of ideas. Perhaps I should use a different way to get the id ? Like using the id in the url ?

Any thoughts and piece of advise is much appreciated. Thank you.

Upvotes: 2

Views: 1718

Answers (2)

Marian Pop
Marian Pop

Reputation: 361

So I finally sorted this out. I had to CONCAT "FirstName" and "LastName" in order to get the id because without CONCAT, my code was comparing the $request->input against both columns (FirstName, LastName) and was never a match.

The working query in JobsController:

$field = DB::raw("CONCAT(`FirstName`, ' ', `LastName`)");
$query = $request->input('custName');

$job =  new Jobs;
$job->cust_id = Customers::where($field, "LIKE", "%{$query}%")->value('id');

Thanks everyone for your time. Really appreciated.

Upvotes: 0

Ganesh Khartode
Ganesh Khartode

Reputation: 71

I use below code working fine

My controller function 

public function Search(Request $request)
{
    $data = Account::Join('groups','groups.group_id','=','accounts.group_id')->select("accounts.name","accounts.account_id","groups.group_name")
            ->where("name","LIKE","%".Input::get('query')."%")
            ->get()->toArray();

    return response()->json($data);
}

my twitter typeahead 

var data = new Bloodhound({
          datumTokenizer: Bloodhound.tokenizers.obj.whitespace('value'),
          queryTokenizer: Bloodhound.tokenizers.whitespace,
          prefetch: "{{route('accounts/search')}}",
          remote: {
            url: "{{url('accounts/search')}}?query=%query",
            transform: function(list) {
                    return $.map(list, function(k,v) { return { id: k.account_id, name: k.name,group:k.group_name }; });
                },
            wildcard: '%query'
          }
        });

        $('.typeahead').typeahead(null, {
            hint: true,
            limit:100,
            highlight: true,
            minLength: 1,
            name: 'Accounts',
            displayKey: function(k) {
               return k.name + " - ("+k.group+")";
            },
              source: data,
              templates: {
                empty: [
                  '<div class="empty-message">',
                    '<span><strong>Account Not Found</strong></span><br/>',
                    '<button type="button" class="btn btn-info" id="account_modal">+ Add New Account</button>',
                  '</div>'
                ].join('\n'),
              }
        }).on('typeahead:selected', function (e, suggestion, name) {
            $(this).parent().next().val(suggestion.id);
            $(this).parent().next().next().remove();
        });

Upvotes: 1

Related Questions