Using DB::raw in where() with Eloquent adds 'is null' to the SQL query

[nikoskip]

I'm trying to user DB::raw in the where() method of my query using Eloquent but for some reason is adding 'is null' after my where clause.

This is my code:

People::has('user')->where(DB::raw('first_name = \'Niko\''))->orderBy($sort, $order)->paginate($perPage););

It execute these SQL's:

0.67ms  select count(*) as aggregate from `people` where (select count(*) from `users` where `users`.`people_id` = `people`.`id`) >= '1' and first_name = 'Niko' is null
0.69ms  select * from `people` where (select count(*) from `users` where `users`.`people_id` = `people`.`id`) >= '1' and first_name = 'Niko' is null order by `id` desc limit 1 offset 0

But if I try this way:

People::has('user')->where('first_name', 'Niko')->orderBy($sort, $order)->paginate($perPage);

Generate these SQL's:

0.94ms  select count(*) as aggregate from `people` where (select count(*) from `users` where `users`.`people_id` = `people`.`id`) >= '1' and `first_name` = 'Niko'
0.72ms  select * from `people` where (select count(*) from `users` where `users`.`people_id` = `people`.`id`) >= '1' and `first_name` = 'Niko' order by `id` desc limit 1 offset 0

As you can see are the same SQL's but without 'is null' after WHERE. This is only an example, I know that I can use the second method but I need to use DB::raw because I will generate complex clauses.

I have the lastest version of Laravel.

[taylorotwell]

Use whereRaw and just pass it a string.

[nikoskip]

whereRaw is new to me. I never heard about it, thanks a lot @taylorotwell.

Exists a way to nest where clausules using Eloquent (not using whereRaw)?.

BTW, I pull some changes to docs some days ago, will be great if you can accept them.

[franzliedke]

You can pass closures to the where() function: http://laravel.com/docs/queries#advanced-wheres

On Mon, Jun 10, 2013 at 3:50 AM, Nikolas notifications@github.com wrote:

whereRaw is new to me. I never heard about it, thanks a lot @taylorotwellhttps://github.com/taylorotwell .

Exists a way to nest where clausules using Eloquent (not using whereRaw)?.

BTW, I pull some changes to docs some days ago, will be great if you can accept them.

— Reply to this email directly or view it on GitHubhttps://github.com/laravel/framework/issues/1591#issuecomment-19178058 .

[nikoskip]

Thanks for your advice @franzliedke

[jedwards1211]

This was a big help to me, thanks @taylorotwell!

[caleywoods]

This just saved my ass from a long day of troubleshooting. Thanks @taylorotwell.

For anyone who comes later and may be needing to use some kind of sql function like to_char or extract (I'm on Oracle) I'm also doing a whereHas. Here's what I had:

$request = Request::all();

$interface_type_id = $request['interface_type_id'];
$month             = $request['month'];

$data = CommissionActivityDetail::with('CommissionActivity.Type',
                                       'AcctInfo.employee'
        )->whereHas('CommissionActivity.Type', function($q) use ($interface_type_id, $month) {
            $q->where('interface_type_id', '=', $interface_type_id)
              ->whereRaw("extract(month from commission.commission_activity_detail.created_date) = $month");
})->get();

This allows me to return details about entries of a specific type (think payroll or reimbursement) for a specific month period 1 (Jan) through 12 (December). I had to go the whereHas route because my parent table doesn't contain a foreign key back over to the type table but instead has a foreign key which points to another table (called interface_activity) which then has an FK over to the type table. Looks something like this, climbing up the tree:

Activity Detail->[FK] Activity->[FK] Interface Activity-> [FK] Interface Type

[otherguy]

@taylorotwell that seems to work. But is it a bug with DB::raw()?

[Cadey]

Hi Guys, this is an odd one and I too got stumped by it for a while...

where(DB:raw('blar'))

vs

whereRaw('blar')

But why would DB::raw() work this way and add the extra is null to the end?

[gabrielkoerich]

@caleywoods your query is not secure.

You must use prepared statements:

->whereRaw('extract(month from commission.commission_activity_detail.created_date) = ?', [$month]);

[caleywoods]

@gabrielkoerich Did some research on this, I'll be sure and use your example. Does this make sure the bind variables are used in oracle or is this just DB agnostic and a best practice?

Edit: I did some more testing on this. We're using a plugin to allow Lumen (laravel micro) to talk to the oracle database. Oracle uses bind variables on every query. I was unable to get any malicious type of input to execute with it failing on syntax. You can see the generated error here, the :autoparam0, :autoparam1 is the plugin doing its job preparing the query for oracle:

https://gist.github.com/caleywoods/c4aed3a532c7b261cdff

[jkunwar]

public function retrieveAppuserAnswers(Request $request) { $answers = DB::table('answers')->whereIn('question_id', function($query){ $query->select('id')->from('questions')->where('appuser_id','=', $request->appuserId); })->get(); return $answers; }

i am getting undefined variable request error in this case. How can i pass variable in where clause in above code.

[leonardyhuang]

@jkunwar , add "use ($request)" in your callback.

public function retrieveAppuserAnswers(Request $request)
{
    $answers = DB::table('answers')->whereIn('question_id', function ($query) use ($request) {
        $query->select('id')->from('questions')->where('appuser_id', '=', $request->appuserId);
    })->get();

    return $answers;
}

https://stackoverflow.com/questions/35035355/how-to-pass-argument-in-callback-function-in-php

[moebaca]

@gabrielkoerich - Great comment preaching on safe programming practices. Also, congrats on your victory against Mexico today!

[gabrielkoerich]

@moebaca thanks bro!

[sfdsfdsfdesfdsghefasefs]

"Use whereRaw and just pass it a string."

Wow Amazing ans Thanks owner

[nilaf123]

"Use whereRaw and just pass it a string." It's works Perfect

[dereks]

But is it a bug with DB::raw()? But why would DB::raw() work this way ...?

@taylorotwell, @nikoskip

This is still a bug in DB::raw(). The 'is_null' appended at the end of the query is just plain wrong. A bug should not be closed just because whereRaw() exists as a workaround.

Please re-open this ticket so it can get fixed.

[Sergey-Dyachenko]

Use whereRaw and just pass it a string.

It'helps me too thanks Taylor