Laravel concat query return 0 items

Question

Here is my controller code:

if ($request->exists('tipo')) {
    $valor = $request->value;
    $candidates = Candidate::buscarpor($tipo, $valor)
                           ->orderBy('id', 'desc')
                           ->Paginate(5)
                           ->withQueryString();
    dd($candidates);
}

And this is the scope "buscarpor" inside my "Candidate" model:

public function scopeBuscarpor($query, $tipo, $valor)
{
    if(($tipo) && ($valor)) {
        if($tipo == 'names') {
            // return $query->orWhereRaw("concat(name, ' ', last_name) like '%".$valor."%' ");
            return $query->where(DB::raw("CONCAT('name', ' ', 'last_name')"), 'like', '%'.$valor.'%')
                         ->orWhere(DB::raw("CONCAT('last_name', ' ', 'name')"), 'like', '%'.$valor.'%');
        }
            
        return $query->where($tipo, 'like', "%$valor%");
    }
}

When the search is of type "names" I should query in the DB to search a candidate/person by using his first name or last name, I only have one input type text, I just writting all his names is this input type text.

The variable $valor inside of this scope has data and no problem with it.. I tested adding a name that exists in my database but it returns 0 items.

This my dd($candidates) output.

I don't know what I'm doing wrong, please guys if you have some idea about how fix this problem, I will appreciate it.. Thanks so much.

IGP · Accepted Answer · 2021-07-04 05:53:09Z

1

Maybe the where expressions are not correctly formed. Try writing it like this

$query->where(DB::raw("CONCAT('name', ' ', 'last_name') like '%?%'", [$valor]))
      ->orWhere(DB::raw("CONCAT('last_name', ' ', 'name') like '%?%'", [$valor]));

You can also use the whereRaw() syntax instead

$query->whereRaw("CONCAT('name', ' ', 'last_name') like '%?%'", [$valor])
      ->orWhereRaw("CONCAT('last_name', ' ', 'name') like '%?%'", [$valor]);

Or maybe the names are written with capital letters in the database? If you are using postgreSQL, you can use ilike instead of like for a case insensitive search.

If you're using MySQL, you might have to use lower() in your raw methods.

$query->whereRaw("LOWER(CONCAT('name', ' ', 'last_name')) like '%?%'", [$valor])
      ->orWhereRaw("LOWER(CONCAT('last_name', ' ', 'name')) like '%?%'", [$valor]);

answered Jul 4, 2021 at 5:53

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Freddy Daniel Over a year ago

Thanks so much bro.. But I also I can't get results.. the query does not returns any result. Here is a pick of my bd imgur.com/a/jzni14V I also I'm using softdeletes.. but that query should returns all the results.. I tested ->whereNull('deleted_at'); but nothing happens.

IGP Over a year ago

Are you using mysql?

IGP Over a year ago

A possibility is to replace every space in the search string with a wildcard character. ($valor = str_replace(' ', '%', $valor);).

Freddy Daniel Over a year ago

Finally we fix this problem using:

return $query->where(function ($q) use ($valor) {                     $q->whereRaw("lower(concat(name, ' ', last_name)) like '%$valor%'")                           ->orWhereRaw("lower(concat(last_name, ' ', name)) like '%$valor%'");                 });

thanks so much bro..

Collectives™ on Stack Overflow

Laravel concat query return 0 items

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related