Syntax error with single quotes in Laravel 5 Eloquent Query Builder

Question

I've been trying to write a massive update query with a where clause, and I've found that Laravel 5.3 Eloquent Query Builder makes a syntax error.

The query is the following:

$query = $this->model
    ->where('state', "pending")
    ->whereRaw('created_at <= NOW() - INTERVAL 12 HOUR')
    ->update(['state' => "timeout"]);

Which is translated to this:

update `orders`
set `state` = timeout, `updated_at` = 2016-09-21 21:47:39
where `state` = pending and created_at <= NOW() - INTERVAL 12 HOUR

This query keeps failing since the values for the columns state and updated_at are not written with single quotes.

How can I force Laravel to write to put the single quotes correctly?

Mārtiņš Briedis · Accepted Answer · 2016-09-21 22:08:54Z

1

Maybe try where('created_at', DB::raw('NOW() - ..'));

And why are you putting peding in single quotes? Laravel escapes values automatically.

answered Sep 21, 2016 at 22:08

Mārtiņš Briedis

17.9k5 gold badges59 silver badges78 bronze badges

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

5 Comments

Mumpo Over a year ago

I knew that I can use DB::raw, but I'd like to find a more... elegant solution, sort to speak. I'm using double quotes because the query builder is removing them too if I use single ones... Maybe this is related to the main problem? (Btw state is an enum)

Mārtiņš Briedis Over a year ago

Enum should not make a difference, because a string is sent to mysql anyway. I thinks that DB::raw would be a more elegant solution. If you start hacking like this, then I suggest just writing a raw query.

Mumpo Over a year ago

I'd rather use the Query Builder tools. I've edited the question to include the single quotes problem in the state column, I think they might be related

Mumpo Over a year ago

Ok, nevermind, this works, sorry for the inconvenience

KAMAEL Over a year ago

@MārtiņšBriedis why not to use where('created_at', '<=', Carbon::now()->addHours(-12)) ?

Daud khan · Accepted Answer · 2017-08-31 09:38:54Z

0

Use something like

$results  = MyModel::where('user_id', Auth::user()->id)
            ->where('created_at','>=',\DB::raw('"'.$from.'"'))
            ->where('created_at','<=',\DB::raw('"'.$to.'"'))
            ->orderby('id','desc')
            ->paginate(15);

Hope this helps.

answered Aug 31, 2017 at 9:38

Daud khan

2,5192 gold badges16 silver badges19 bronze badges

Collectives™ on Stack Overflow

Syntax error with single quotes in Laravel 5 Eloquent Query Builder

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related