CODEWITHSUNDEEP
phpmysqllaravellaravel-4
Milkncookiez
Milkncookiez

Reputation: 7407

DATE(column) filter returns nothing

If I run the following query directly in phpmyadmin I get the desired result

select * from trips where route_from = 'CityA' and route_to = 'CityB' and DATE(start_date) = '2015-04-07'

But if I run it as PHP code, it returns an empty result

Trip::whereRouteFrom('CityA')
                ->whereRouteTo('CityB')
                ->where(\DB::raw('DATE(start_date)', '=', '2015-04-07'))->get();

The SQL code that the ORM executes is the following (I am getting it through ->toSql()):

select * from `trips` where `route_from` = ? and `route_to` = ? and DATE(start_date) is null

Now, I guess the problem is in DATE(...) is .... When I run the SQL code with is in phpmyadmin, it gives me syntax error. With = works fine. But the framework generates it with is, so I am not sure whether that's the problem or there is something else.

Upvotes: 0

Views: 47

Answers (2)

mininoz
mininoz

Reputation: 5958

Are you trying to use whereRaw?

If so the code should look right this.

Trip::whereRouteFrom('CityA')
                ->whereRouteTo('CityB')
                ->whereRow('DATE(start_date) = "2015-04-07"'))
                ->get();

Or if you want to pass dynamic value you can pass it at the second parameter.

$date = "2015-04-07";
Trip::whereRouteFrom('CityA')
                    ->whereRouteTo('CityB')
                    ->whereRow('DATE(start_date) = ?', array($date) )
                    ->get();

Upvotes: 1

Limon Monte
Limon Monte

Reputation: 54459

You have typo, instead of:

->where(\DB::raw('DATE(start_date)', '=', '2015-04-07'))->get();

you should use:

->where(\DB::raw('DATE(start_date)'), '=', '2015-04-07')->get();

Upvotes: 2

Related Questions