Laravel merge two columns when querying LIKE

Question

I have a search input ?search=xxxx

I have two columns in my database first_name and last_name. I want the search to work for both of them, for example if my input is John Smith the search should be like CONCAT(first_name, last_name) ILIKE %input%

But laravel doesn't let me do it:

$customers = $customers->selectRaw("CONCAT('first_name', 'last_name') AS fullname")->where("fullname", "ilike", "%" . $text . "%");

Customers is already defined, and is an ORM object, not collection.

And the error i get for this:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "fullname" does not exist LINE 1: ...) AS fullname from "people" where "type" = $1 and "fullname"... ^ (SQL: select CONCAT('first_name', 'last_name') AS fullname from "people" where "type" = customer and "fullname" ilike %fidel% order by "id" asc)

Also tried:

 $customers = $customers->whereRaw("CONCAT('first_name', 'last_name')", "ilike", "%" . $text . "%");

And another error:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (SQL: select * from "people" where "type" = customer %fidel% CONCAT('first_name', 'last_name') order by "id" asc)

Have you tried just regular string concatenation? ->whereraw("COALESCE(first_name + ' ' + last_name, '') LIKE '%$query%'"). I'm not great with SQL, so I'm not sure what ILIKE is vs LIKE, but that's our search function for first and last name and it works pretty good. — N Mahurin
– N Mahurin, Commented Aug 14, 2018 at 14:34
This is interesting. The sql looks valid, except for the double quotes around column name, not sure why it would throw an undefined column. It's very strange however, that laravel add double quotes around the column names. They should not be there. — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Aug 14, 2018 at 14:44
try this: $result = Customers::where(DB::raw('concat(first_name," ",last_name)') , 'LIKE' , '%keyword%')->get(); let us know if it's helpful. — Sahith Vibudhi
– Sahith Vibudhi, Commented Aug 14, 2018 at 15:18

Jonas Staudenmeir · Accepted Answer · 2018-08-14 16:09:12Z

3

There are multiple problems:

You can't wrap columns in single quotes. Use double quotes (or no quotes at all).
You can't access derived columns like fullname in the WHERE clause.
whereRaw() only has two parameters, the SQL string and an array of bindings.

Use this:

$customers->where(DB::raw('concat("first_name", "last_name")'), 'ilike', '%'.$text.'%');

answered Aug 14, 2018 at 16:09

Jonas Staudenmeir

26k7 gold badges70 silver badges115 bronze badges

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

Comments

Bob The Builder · Accepted Answer · 2018-08-14 15:02:52Z

0

I'm not sure what it is you are using based on how you are writing your code but if it is sql this might be what you are after...

DECLARE @cust_name VARCHAR(100) = 'john smith'  -- users input
DECLARE @first_name VARCHAR(50) = SUBSTRING(@cust_name, 1, CHARINDEX(' ', @cust_name) - 1)
DECLARE @last_name VARCHAR(50)  = SUBSTRING(@cust_name, CHARINDEX(' ', @cust_name) + 1, 8000) 


SELECT CONCAT(c.FirstName,c.LastName)
FROM 
    customers c
WHERE
    c.FirstName = @first_name
    AND c.LastName = @last_name

answered Aug 14, 2018 at 15:02

Bob The Builder

11 bronze badge

Collectives™ on Stack Overflow

Laravel merge two columns when querying LIKE

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related