CODEWITHSUNDEEP
databaselaraveleloquent
Freddy Daniel
Freddy Daniel

Reputation: 389

Laravel concat query return 0 items

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.

enter image description here

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.

Upvotes: 0

Views: 130

Answers (1)

IGP
IGP

Reputation: 15879

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

Upvotes: 1

Related Questions