CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-5.4
Lakmal Premaratne
Lakmal Premaratne

Reputation: 1229

Laravel multiple WHERE clauses

I have a requirement to add multiple where clauses to a Laravel SQL query.

So far my PHP code has been:

date_default_timezone_set('America/Los_Angeles');

$today = getdate();
$year = $today['year'];
$month = $today['mon'];
$day = $today['mday'];

$today_ = $day.'-'.$month.'-'.$year;
$result = DB::table('task')
    ->select('*')
    ->where(
        ['rowstate', '<>', 'Ready'],
        ['DATE_FORMAT(due_date, "%d-%m-%y")', '<', $today_])
    ->get();

But above code returns:

Column not found: 1054 Unknown column '0' in 'where clause' 
(SQL: select * from `task_tab` where (`0` = rowstate and `1` = <> and `2` = Ready))

I want to generate below SQl statement:

SELET * 
FROM task
WHERE rowstate <> 'Ready'
AND DATE_FORMAT(due_date, "%d-%m-%y") < $today

Upvotes: 3

Views: 22564

Answers (3)

Sergii K
Sergii K

Reputation: 866

Try this

->where([
//      ^
    ['rowstate', '<>', 'Ready'],
    ['DATE_FORMAT(due_date, "%d-%m-%y")', '<', $today_]])
//                                                     ^

Upvotes: 0

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111829

You have 2 possible solutions.

Instead of:

->where(['rowstate', '<>', 'Ready'], ['DATE_FORMAT(due_date, "%d-%m-%y")', '<', $today_])

you can use

->where('rowstate','<>','Ready')->where(DB::raw('DATE_FORMAT(due_date, "%d-%m-%y"','<', $today_);

or

you can use array syntax like this:

->where([
    ['rowstate', '<>', 'Ready'], 
    [DB::raw('DATE_FORMAT(due_date, "%d-%m-%y")'), '<', $today_]
 ]);

So to sum up you were missing enclosing your data into outer array, and you need to use DB::raw in case you don't use raw column names

Upvotes: 7

Pankit Gami
Pankit Gami

Reputation: 2553

You can't use sql function's directly in laravel where class. You need to use DB::raw() for same.

So your query will be like :

$result = DB::table('task')
    ->select('*')
    ->where('rowstate', '<>', 'Ready')
    ->where(DB::raw('DATE_FORMAT(due_date, "%d-%m-%y")'), '<', $today_)
    ->get();

Upvotes: 0

Related Questions