Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

developerchick's avatar

Performing concat on where clause

I have a question, how do you go about using the where clause and performing concatenation. MySQL query: select * from users where concat(name, ' ', surname) LIKE '%John%' (using this as an example because if someone puts in "John Robinson" in the search box, we want them to see just that result. The query works fine in MySQL but I am having a hard time doing this in Laravel in the where statement. Part of my original statement that I am modifying received from different devs:

])->where ('name', 'LIKE', '%' . $keyword . '%') ->orWhere('surname', 'LIKE', '%' . $keyword . '%') ->get();

This gives us all Johns but if someone puts in John Robinson, it gives us only people with the last name Robinson. I am a noob and sorry if this is a stupid question.

0 likes
8 replies
Snapey's avatar

You need an 'and' since you only want matches for the name AND the surname.

I think a straight Where is applied as an And. Try changing orWhere to just Where.

developerchick's avatar

That doesn't return anything. Can you tell me instead how to write the concat statement into the where? That gives me exactly what records I need.

Snapey's avatar
Snapey
10 years ago
Best Answer
Level 122

Sorry, I was thinking your search terms were separate fields. Time to resort to a little DB::raw

$result = User::where(DB::raw('concat(name," ",surname)') , 'LIKE' , '%keyword%')->get();

It's unusual but you will get no search results if the user enters extra spaces anywhere, before, after or in the middle. Its an idea to replace all spaces with % before running the query.

6 likes
developerchick's avatar

Thank you, that worked. I also had to do "use db;" at the top as well, which they didn't have before. I appreciate your time.

Snapey's avatar

mark as the right answer please?

1 like
sghimire's avatar

Hi Snapey,

I am trying to do the concatenate "first_name and last_name" of customer but it is throwing error as below, please can you help on this.

$orders = (new Order)->newQuery()->select('concat(customer_contacts.first_name," ",customer_contacts.last_name)')
                ->join('samples', 'orders.id', '=', 'samples.order_id' )
                ->join('assignments', 'samples.id', '=', 'assignments.sample_id' )
                ->join('customer_contacts', 'orders.customer_contact_id', '=', 'customer_contacts.id' )
                ->where('orders.id', '=', 1)                
                ->whereNotNull('customer_contacts.email')->first();
        }

 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'concat(customer_contacts.first_name,\" \",customer_contacts.last_name)' in 'field list' (SQL: select `concat(customer_contacts`.`first_name,\" \",customer_contacts`.`last_name)` from `orders` inner join `samples` on `orders`.`id` = `samples`.`order_id` inner join `assignments` on `samples`.`id` = `assignments`.`sample_id` inner join `customer_contacts` on `orders`.`customer_contact_id` = `customer_contacts`.`id` where `orders`.`id` = 234877 and `assignments`.`team_id` = 4 and `customer_contacts`.`email` is not null limit 1)

Thanking in advanced. Mani

Please or to participate in this conversation.