Query fails in Laravel but works in MySQL

Question

I have some query scope methods defined in an Eloquent model and am calling them in the controller to allow Laravel to build the query using

return View::make('credit_apps/view')->with('creditApps', CreditApp::ApprovalStatus()
                                     ->SalesRep()
                                     ->Submitted()
                                     ->RefReceived()
                                     ->get()
                                     );

The error returned by Illuminate is a MySQL syntax error:

Illuminate \ Database \ QueryException SQLSTATE[42000]: Syntax error
or access violation: 1064 You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near '' at line 1 (SQL: select * from `credit_apps`
where `approval_status` = 'Approved' and `sales_rep` = 'Joe Smith' and
`date_received` between '2014-04-30 00:00:00' AND '2014-05-15
00:00:00' and `date_ref_received` between '2014-04-27 00:00:00' AND
'2014-05-30 00:00:00')

However when I parse the query in MySQL Workbench or from the command line it parses just fine although the criteria returns no records, but I knew it wouldn't. Nevertheless, it's a valid query and works fine in MySQL.

What is causing Laravel to throw the error?

It appears that Laravel doesn't like the dates in the where clause. If I search without dates it works. — Malchesador
– Malchesador, Commented May 15, 2014 at 16:06
I see nothing wrong with the SQL query, but I notice that it says "Syntax error OR access violation". Have you tried running this SQL via the terminal with the same MySQL user/pass used by Laravel? Also, the SHOW CREATE TABLE would be immensely helpful. I assume that the date columns are DATETIME? — charmeleon
– charmeleon, Commented May 15, 2014 at 16:09
Here's an example of one of the scope date methods: ```` public function scopeSubmitted($query) { if (strlen(trim(Input::get('date_received1'))) > 4 && strlen(trim(Input::get('date_received2'))) > 4) { $beg = strftime('%Y-%m-%d %T', strtotime(Input::get('date_received1'))); $end = strftime('%Y-%m-%d %T', strtotime(Input::get('date_received2'))); return $query->where('date_received', 'between', "'" . $beg . "' AND '" . $end . "'"); } } ```` — Malchesador
– Malchesador, Commented May 15, 2014 at 17:47
Btw @charmeleon, I did mention that the query parses just fine from the command line and from within MySQL Workbench, and yes, the columns are datetime data types. Code blocks not allowed in comments??? — Malchesador
– Malchesador, Commented May 15, 2014 at 17:57

Hackerman · Accepted Answer · 2014-05-15 16:13:59Z

1

You should try something like this:

select * from `credit_apps`
where `approval_status` = 'Approved' and `sales_rep` = 'Joe Smith' 
and
(`date_received` between '2014-04-30 00:00:00' and '2014-05-15 00:00:00')
and 
(`date_ref_received` between '2014-04-27 00:00:00' and '2014-05-30 00:00:00')

answered May 15, 2014 at 16:13

Hackerman

12.3k2 gold badges37 silver badges47 bronze badges

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

5 Comments

Malchesador Over a year ago

Do you know how to wrap that in parenthesis using Laravel's where method? return $query->where('date_received', 'between', "'" . $beg . "' AND '" . $end . "'");

OACDesigns Over a year ago

you can use multiple whereBetween: ->whereBetween('column', array($from, $to))

Malchesador Over a year ago

That worked. I'm still irritated that Laravel threw an error on a perfectly valid SQL statement though!~

Malchesador Over a year ago

Just as an FYI, whereBetween does not encapsulate the between clause in parenthesis. So why Laravel is happy with that method rather than using ->where() is seemingly a bit of a mystery.

Hackerman Over a year ago

You could modify that method to escapsulate in parenthesis :)

Collectives™ on Stack Overflow

Query fails in Laravel but works in MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related