laravel Eloquent query WhereHas wrong result

user_6220195 0
Viewed 505

I have two tables and relations as bellow user the user table:

id name active
1 abc 1
2 xyz Null
3 abx 0

the book table:

id user_id name active
1 1 book1 0
2 2 book2 0
3 1 book3 0

relation is as this user->books (HasMany)

return $this->hasMany(Book::class,'user_id','id');

my query is as bellow

User::with('book')
->WhereHas('book', function($query) {
                    $query->where(['active'=> 1]);
                   })
->where(['id'=> 1,'active'=>1])
->get();

This query is getting zero records because active is 0 in books

  1. But i want to see all user record and if there is matching record with active 1 in book.
  2. second is query user for active 1 or Null and for that if use ->orwhereNull('active') All records changes.

Thanks

eloquent laravel
2 Answers

You have id and active column on both table, so I think you need to change your query like this :

User::with('book')
->WhereHas('book', function($query) {
         $query->where(['books.active'=> 1]); // books table active column
})
->where(['id'=> 1,'active'=> 1]) // users table id, active column
->get();
user_45753500

I guess you need to apply filter on related records (with()) instead of applying filter on whole query. So this way you will always get a list of users along with the list of active books only

User::with(['book'=> function($query) {
        $query->where('active', 1);
}])
->where(function ($query) {
    $query->where('active', 1)
          ->orWhereNull('active');
})
->get();

I have used logical grouping here so in case if you have more filter clauses to add in your query.

Or you can define a new relation in your user model to pick only active related books as

class User extends Model
{
    public function book()
    {
        return $this->hasMany(Book::class, 'user_id', 'id');
    }
    public function active_books()
    {
        return $this->hasMany(Book::class, 'user_id', 'id')
            ->where('active', '=', 1);
    }
}


User::with('active_books')
    ->where(function ($query) {
        $query->where('active', 1)
              ->orWhereNull('active');
    })
    ->get();
user_8533600
Related