title: how to get sum with whereIn/whereBetween using laravel query builder?
body: <p>I have getting wrong value from query. can anyone help me to correct my query in laravel project.
  mysql code :</p>
  <pre><code>select SUM(amount) as total from `sales` 
  where `bankid` = 1 
  and `month` = 8 
  and `year` = 2020
  and userid in (select userid from user where bank_id=2 AND (usertype=1 OR usertype=2))
  </code></pre>
  <p>Laravel code :</p>
  <pre><code>function test($bank_id,$usertype,$month=NULL,$year=NULL){
      $query = Sales::query();
      if(is_numeric($bank_id)){
          $query-&gt;where('bankid',$bank_id);
      }
      if($month)
      $query = $query-&gt;where('month', $month);
      
      if($year)
      $query = $query-&gt;where('year', $year);
  
      $query = $query-&gt;select(DB::raw(&quot;SUM(amount) as total&quot;));
      if(is_numeric($usertype)){
          $query-&gt;whereIn('userid',function ($query) use($bank_id,$usertype) {
              $query-&gt;select(DB::raw('userid'))
              -&gt;from('user');            
              if(is_numeric($bank_id)){
                  $query-&gt;where('bank_id',$bank_id);
              }                 
              if($usertype==1){
                 // $query-&gt;whereBetween('usertype', [1, 2]);
                  $query-&gt;where('usertype', 1);
                  $query-&gt;orWhere('usertype', 2);
              } else {
                  $query-&gt;where('usertype',$usertype);
              }
          });
      } 
      return $query-&gt;get()-&gt;toarray()[0]['total'];
  }
  </code></pre>
  <p>When i used querylog and got the query:</p>
  <pre><code>DB::connection()-&gt;enableQueryLog(); 
  dd($query-&gt;toSql(), $query-&gt;getBindings());
  
  select SUM(amount) as total from `slaes` 
  where `bankid` = 1 
  and `month` = 8 
  and `year` = 2020 
  and `userid` in (select userid from `user` where `bank_id` = 1 and `usertype` =1 OR `usertype` = 2)
  </code></pre>
  <p>I need to make it from <code>and userid in (select userid from user where bank_id = 1 and usertype =1 OR usertype = 2)</code> to <code>and userid in (select userid from user where bank_id = 1 and (usertype =1 OR usertype = 2))</code></p>
  <p>And can anyone suggest to minimize loading issue while running this query. i have almost 1M records in my database.<br />
  Thank you.</p>