How to use multiple whereHas with where in laravel

Question

I'm using where with multiple whereHas() and i'm not getting the expected result.

This is my code

public function getCommunityScoreByUser($user,$category_id) {
      $question_ids = $user->answers->pluck('question_id')->unique();

      $specialities = $this->speciality
                    ->whereHas('questions', function($qry) use($question_ids){
                        $qry->whereIn('questions.id', $question_ids);
                       })
                    ->orwhereHas('caseStudies', function($qry) use($user) {
                        $qry->where('user_id', $user->id);
                       })
                    ->where('is_active', 'Y')
                    ->where('category_id',$category_id)
                    ->withCount('answers')
                    ->withCount(['answers as bestAnswerCount' => function ($query) {
                                $query->where('question_answer.is_accepted','Y');
                                }])
                    ->with(['answers' => function ($query) {
                          $query->withCount(['votes' => function ($query) {
                                $query->where('count','1');
                             }]);
                      }])
                    ->get();
      foreach ($specialities as $speciality => $key) {
        $votes_count = 0;
        foreach ($key->answers as $key1 => $value) {
          $votes_count += $value->votes_count;
        }
        $key->votesCount = $votes_count;
        $key->totalVotes = (3*$key->bestAnswerCount)+$votes_count+$key->case_study_votes_count;
      }
      return $specialities;
}  

Expected result

I want to fetch specialities which have either questions, or case-studies and it should meet the criteria ->where('category_id',$category_id)

Actual result

$specialities conatins either questions, or case-studies, But it's not checking the category_id. Instead, it fetches $specialities with all the categories. But if i remove one of the whereHas, then it's working perfectly.

Please help me find the solution.

Answer 1

Using the orWhereHas is probably not behaving the way you expect it to. In your case if

->orwhereHas('caseStudies', function($qry) use($user) {
     $qry->where('user_id', $user->id);
})

is true, then it will be allowed. It will not need to check all other filters.

If you want to check one or the other whereHas you will need to add these into their own where to kind of 'limit' the or.

->where(function($query) use ($question_ids, $user){
    $query->whereHas('questions', function($qry) use($question_ids){
        $qry->whereIn('questions.id', $question_ids);
    })
    ->orwhereHas('caseStudies', function($qry) use($user) {
        $qry->where('user_id', $user->id);
    });
})

Answer 2

The result you get is due to the orWhereHas. With your current statement, it will get all specialities where it has questions OR where it has case studies where is active etc etc.

What you want to do is limit the or to just the whereHas statements.

To do this you can do the following :

$specialities = $this->speciality
    ->where(function ($qry) use ($question_ids, $user) {
        $qry->whereHas('questions', function ($qry) use ($question_ids) {
            $qry->whereIn('questions.id', $question_ids);
        })->orwhereHas('caseStudies', function ($qry) use ($user) {
            $qry->where('user_id', $user->id);
        });
    })
    ->where('is_active', 'Y')
    ->where('category_id', $category_id)
    ->withCount('answers')
    ->withCount(['answers as bestAnswerCount' => function ($query) {
        $query->where('question_answer.is_accepted', 'Y');
    }])
    ->with(['answers' => function ($query) {
        $query->withCount(['votes' => function ($query) {
            $query->where('count', '1');
        }]);
    }])
    ->get();

What this will do is just wrap the whereHas's in their own where so they are treated independently.