Level 47
Instead of whereHas() use has().
Mar 7, 2016
10
Level 13
where value is not equal to.
Hi every body, got a question. I'm trying create a query where i fetch only the users with that has an specific relationship and them filter down only the ones that does not have a year value equal to the pass variable. Not quite sure why every time it returns all the users with the relationship but does not filter down the year. here is the code.
User::whereHas('granteeReports', function($q){
$q->where('year', '=', 2017 );
})->get();
returns 2 records ( expected );
User::whereHas('granteeReports', function($q){
$q->where('year', '<>', 2017 );
})->get();
returns all records of user with granteeReports relationship ( expecting two records diffrent than what previus query returns)
Here are the relationships:
public function granteeReports ()
{
return $this->hasMany('App\GranteeReport');
}
public function user ()
{
return $this->belongsTo(App\User::class);
}
Appreciate any help with it, thanks guys
Level 13
@jekinney Thanks for tip, but not quite sure if i get you, if i change the whereHas() for has() them i filter down with where
App\User::has('granteeReports')->where('year', '<>', 2017)->get()
i get the following error
Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'year' in 'where clause' (SQL: select * from `users` where (select count(*) from `grantees_reports` where `grantees_reports`.`user_id` = `users`.`id`) >= 1 and `year` <> 2017)'
not sure if that is how it supoused to be done.
Level 54
Try
App\User::has('granteeReports')->where('grantees_reports.year', '<>', 2017)->get()
Level 10
First atempt should work, try to
User::whereHas('granteeReports', function($q){
$q->where('year', '!=', 2017 );
})->get();
Level 13
Thanks guys. @willvincent , got this error :
Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'grantees_reports.year' in 'where clause' (SQL: select * from `users` where (select count(*) from `grantees_reports` where `grantees_reports`.`user_id` = `users`.`id`) >= 1 and `grantees_reports`.`year` <> 2017)'
@cipsas As you said, it should work, but for some reason the query returns all the users with reportsGrantee relationship, and does not filter down the year, here is the generate it query
select * from `users` where (select count(*) from `grantees_reports` where `grantees_reports`.`user_id` = `users`.`id` and `year` != ?) >= 1
Level 54
Perhaps a silly question, but are you sure the year column is an integer?
Level 13
@willvincent Not at a silly quesetion, and yes it is an integer.
Level 3
You can try this breakneck solution with chaining where condition, something like this:
->where('year', '<', 2017 )->where('year', '>', 2017 )->get();
Year is less than and greater than, and the condition is completed, So, the specified year is not equal
Level 41
@loreias You're probably looking for the whereDoesntHave method.
User::whereDoesntHave('granteeReports', function($q){
$q->where('year', '=', 2017 );
})->get();
4 likes
Level 2
I was having a similar problem @thomaskim had the right answer. I don't think the method is listed in the docs but it can be seen in the API here: https://laravel.com/api/5.2/Illuminate/Database/Eloquent/Builder.html#method_whereDoesntHave
Please or to participate in this conversation.