Laravel concat in query (where condition)

Question

I am new to laravel and face a problem building a simple query:

$query->orWhere("CONCAT(`nvp`, ' ', `vpv`)", 'LIKE', "%$this->searchNeedle%");

This line above is one of several conditions in an encapsulated query condition. I think the other lines are not necessary for this case but tell me if you need to see them.

I found out that the developer decided to add a

`

before and after the first orWhere/where param which cause the problem that I cant use a simple concat, because the line above will result in:

`CONCAT(`vpv`, ' ', `nvp`)` LIKE ?)' 
↑                         ↑
this                    & this

Since this is automagically added i cant remove it without overwriting a laravel-core function which i wont. Is there any SQL-based "hack" that handles these two ` ? Something in the way like 1 = 1, you know?

Maybe you have another solution for me to get the intended result, comparing one string with two rows in combination?

Maybe it works also [iterare where () by request on for loop ](stackoverflow.com/a/71666425/14664943) — heriberto hernandez torres
– heriberto hernandez torres, Commented Mar 29, 2022 at 17:37

user1669496 · Accepted Answer · 2014-08-11 13:36:23Z

64

Laravel does some stuff behind the scenes like adding in the tick marks for you.

Fortunately, it also offers a couple of tools to still get the job done for you...

For this type of thing, DB::raw() usually works really well. Try something like this...

$query->orWhere(DB::raw("CONCAT(`nvp`, ' ', `vpv`)"), 'LIKE', "%".$this->searchNeedle."%");

answered Aug 11, 2014 at 13:36

user1669496

33.3k9 gold badges76 silver badges65 bronze badges

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

4 Comments

Jonathan Over a year ago

Now the App wont crash but result in this: CONCAT(nvp, ' ', vpv) LIKE '%users name%' is null

user1669496 Over a year ago

I think my syntax was a little off, I just modified the answer.

Moses Ndeda Over a year ago

I smell sql injection with this answer,` $this->searchNeedle` is coming directly from the user.

user1669496 Over a year ago

Laravel will handle the parameter binding for you so no chance of SQL injection. If the entire thing was wrapped in DB::raw() you'd be correct.

Joseph Silber · Accepted Answer · 2018-01-05 12:17:48Z

20

Use orWhereRaw to execute a raw where query:

$query->orWhereRaw("CONCAT(`nvp`, ' ', `vpv`) LIKE ?", ['%'.$this->searchNeedle.'%']);

edited Jan 5, 2018 at 12:17

answered Aug 11, 2014 at 14:46

Joseph Silber

221k59 gold badges369 silver badges292 bronze badges

3 Comments

mhellmeier Over a year ago

It doen't work for me (SQL error). I fixed it by putting the % into the second argument: $query->orWhereRaw("CONCAT(nvp, ' ', vpv) LIKE ?", ["%".$this->searchNeedle."%"]);

Istiaque Ahmed Over a year ago

may I ask you to have a look at a Laravel table join related question here : stackoverflow.com/questions/52149031/… ?

Abdullah Shakir Over a year ago

You must use ` instead of ' for the columns.

Collectives™ on Stack Overflow

Laravel concat in query (where condition)

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related