Laravel where how doesn’t work properly

Question

I would like to use the query builder with the LIKE operator, but it does not work properly.

Here is my code in the controller:

public function listall($query) { $Clubs = Club::where('clubs.name', 'like', "%$query%") ->Join('leagues', 'clubs.league_id', '=', 'leagues.id') ->select('clubs.id', 'clubs.name', 'clubs.blason', 'leagues.name as league_name') ->orderBy('clubs.name') ->get(); return Response::json($Clubs); } 

Here is my Javascript code:

 <script type="text/javascript"> function hasard(min,max){ return min+Math.floor(Math.random()*(max-min+1)); } jQuery(document).ready(function($) { // Set the Options for "Bloodhound" suggestion engine var engine = new Bloodhound({ remote: { url: "{{ url('club/listall') }}"+'/%QUERY%', wildcard: '%QUERY%' }, datumTokenizer: Bloodhound.tokenizers.obj.whitespace, queryTokenizer: Bloodhound.tokenizers.whitespace }); $(".club-search").typeahead({ hint: true, highlight: true, minLength: 1 }, { source: engine.ttAdapter(), display: "name", // This will be appended to "tt-dataset-" to form the class name of the suggestion menu. name: 'clubsList', // the key from the array we want to display (name,id,email,etc...) templates: { empty: [ '<div class="list-group search-results-dropdown"><div class="list-group-item">Aucun club trouvé.</div></div>' ], header: [ '<div class="list-group search-results-dropdown">' ], suggestion: function (data) { if (data.blason == null) { var aleat = hasard(1,4); if (aleat == 1) { var blason = "/images/blasons/blason-bleu.svg"; } else if (aleat == 2) { var blason = "/images/blasons/blason-orange.svg"; } else if (aleat == 3) { var blason = "/images/blasons/blason-rouge.svg"; } else if (aleat == 4) { var blason = "/images/blasons/blason-vert.svg"; } } else { var blason = "/images/blasons/" + data.blason; } return '<a href="{{ url('club') }}' + '/' + data.id + '" class="list-group-item"><span class="row">' + '<span class="avatar">' + '<img src="{{asset('/')}}' + blason + '">' + "</span>" + '<span class="name">' + data.name + '<br><small style="color:grey;">(Ligue ' + data.league_name + ')</small></span>' + "</span>" } } }); }); </script> 

But it does not work completely correctly ... In general, it finds the results, but I will give an example of a search query. One possible query is "montagnarde". I will give you the result for each letter. Typing:

 m --> lot of results mo --> lot of results mon --> lot of results mont --> lot of results monta --> lot of results montag --> lot of results montagn --> lot of results montagna --> no result montagnar --> finds only "JS MONTAGNARDE" montagnard --> finds only "JS MONTAGNARDE" montagnarde --> finds only "JS MONTAGNARDE" and "LA MONTAGNARDE" montagnarde i --> finds only "US MONTAGNARDE INZINZAC" 

Does anyone see where the problem is? Thank you in advance!

Answer 1

I think your string concatenation is wrong.

Try changing the where statement to

 where('clubs.name', 'LIKE', '%' . $query. '%') 

Answer 2

@Dealeo You can write this. Hope this solves your problem.

 public function listall($query) { $Clubs = Club::Join('leagues', 'clubs.league_id', 'leagues.id') ->where('clubs.name', 'LIKE', '%' . $query . '%') ->select('clubs.id', 'clubs.name', 'clubs.blason', 'leagues.name as league_name') ->orderBy('clubs.name') ->get(); return Response::json($Clubs); } 

Answer 3

now:

public function listall ($ query) {

 dd( $query ); $clubs = Club::join('leagues', 'clubs.league_id', '=', 'leagues.id') ->where('clubs.name', 'LIKE', '%' . $query . '%') ->select('clubs.id', 'clubs.name', 'clubs.blason', 'leagues.name as league_name') ->orderBy('clubs.name'); dd( $clubs->toSql() ); return Response::json($clubs); 

}

Answer 4

You may need to use the search query as follows:

 ->where('clubs.name', 'like', "%{$query}%") 

Answer 5

I suggest you add COLLATE UTF8_GENERAL_CI to the table definition, and then try your query like this (using leftJoin):

 public function listall($query) { $Clubs = Club::leftJoin('leagues', 'clubs.league_id', '=', 'leagues.id') ->where('clubs.name', 'like', "%$query%") ->select('clubs.id', 'clubs.name', 'clubs.blason', 'leagues.name as league_name') ->orderBy('clubs.name') ->get(); return Response::json($Clubs); } 

Combine utf8 clause from: How can I search (case insensitive) in a column using the LIKE pattern?