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
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
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
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

Hackerman Hackerman · Accepted Answer · 2014-05-15T16:13:59

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')

Query fails in Laravel but works in MySQL

1 Answers