how to get sum with whereIn/whereBetween using laravel query builder?

Question

I have getting wrong value from query. can anyone help me to correct my query in laravel project. mysql 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))

Laravel code :

function test($bank_id,$usertype,$month=NULL,$year=NULL){
    $query = Sales::query();
    if(is_numeric($bank_id)){
        $query->where('bankid',$bank_id);
    }
    if($month)
    $query = $query->where('month', $month);
    
    if($year)
    $query = $query->where('year', $year);

    $query = $query->select(DB::raw("SUM(amount) as total"));
    if(is_numeric($usertype)){
        $query->whereIn('userid',function ($query) use($bank_id,$usertype) {
            $query->select(DB::raw('userid'))
            ->from('user');            
            if(is_numeric($bank_id)){
                $query->where('bank_id',$bank_id);
            }                 
            if($usertype==1){
               // $query->whereBetween('usertype', [1, 2]);
                $query->where('usertype', 1);
                $query->orWhere('usertype', 2);
            } else {
                $query->where('usertype',$usertype);
            }
        });
    } 
    return $query->get()->toarray()[0]['total'];
}

When i used querylog and got the query:

DB::connection()->enableQueryLog(); 
dd($query->toSql(), $query->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)

I need to make it from and userid in (select userid from user where bank_id = 1 and usertype =1 OR usertype = 2) to and userid in (select userid from user where bank_id = 1 and (usertype =1 OR usertype = 2))

And can anyone suggest to minimize loading issue while running this query. i have almost 1M records in my database.
Thank you.

Dmitry · Accepted Answer · 2020-08-28 14:09:17Z

1

To get your expression inside brackets in Laravel ORM you need to use callback function inside where like this:

$query->where(function($query) {
  return $query
    ->where('usertype', 1)
    ->orWhere('usertype', 2);
});

But in your case you can also use shortest method:

$query->whereIn('usertype', [1,2]);

answered Aug 28, 2020 at 14:09

Dmitry

1,7077 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

suba Over a year ago

i have try with $query->whereIn('usertype', [1,2]); also. but It is also taking much time to return response.

Collectives™ on Stack Overflow

how to get sum with whereIn/whereBetween using laravel query builder?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related