Laravel where like not working properly

Question

I would like to use the query builder with the LIKE operator but it's not working properly.

Here is my code in my 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 its not working completely properly... In general, it finds results, but I'll give u an example of a search query. One possible query is "montagnarde". I'll give you the result for every 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 "J.S. MONTAGNARDE"
montagnard --> finds only "J.S. MONTAGNARDE"
montagnarde --> finds only "J.S. MONTAGNARDE" and "LA MONTAGNARDE"
montagnarde i --> finds only "U.S. MONTAGNARDE INZINZAC"

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

Oldfashioned

Instead of using ->get()at the end of your query, use ->toSql() and then print or dd() the result. This will show you what query is actually being run - useful debugging like you are doing now. – Brennan

Here is the result : "select clubs.id, clubs.name, clubs.blason, leagues.name as league_name from clubs inner join leagues on clubs.league_id = leagues.id where clubs.name like ? order by clubs.name asc" – Oldfashioned

Answer 1

I think your string concatenation is wrong.

Try to change where statement to

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

Allo

Thank you for your answer, but this is the same result with : where('clubs.name', 'LIKE', '%' . $query. '%') or where('clubs.name', 'LIKE', "%$query%") – Oldfashioned

There's nothing wrong with using "%$query%". PHP interpolates variables in double quoted strings. – Taddeusz

Answer 2

Try this

$Clubs = Club::where(DB::raw('LOWER(clubs.name)'), 'LIKE', '%'.strtolower($query).'%')
->Join('leagues', 'clubs.league_id', '=', 'leagues.id')
->select('clubs.id', 'clubs.name', 'clubs.blason', 'leagues.name as league_name')
->orderBy('clubs.name')
->get();

Derouen

Answer 3

@Dealeo You can write this. Hopefully this will solve 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);
    }

Appendectomy

Thank you for your answer, but I have the same results with your code. – Oldfashioned

@Dealeo-JeromeMansbendel I have updated my answer. Would you try this? – Appendectomy

Answer 4

check 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);

}

Canikin

Answer 5

You may have to use the search-query like this:

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

Tropical

Answer 6

I suggest you to add COLLATE UTF8_GENERAL_CI to your table definition and then try your query like this (with 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);
    }

Collate utf8 suggestion from: How can I search (case-insensitive) in a column using LIKE wildcard?

Accrue

Answer 7

MySQL like statements and "%xyz spaces-bla-bla 123%" doesn't work. It's not a Laravel problem.

Prunella