When i write a join query in laravel, it get 'Allowed memory exhausted'

I wrote join query in laravel project. there are 1M records in the database. when i load the page it is showing 'Allowed memory exhausted' message. can anyone help me to solve this issue? My code:

function test($bank_id=NULL,$month=NULL,$year=NULL,$userType=NULL){
    $query = Sales::query();
    if($bank_id){
        $query = $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($userType!="all"){   
        $query->join('user', function($join)
        {
            $join->on('user.userid', '=', 'sales.userid');
            $join->on('user.bank_id', '=', 'sales.bankid');
        });
        if($userType==1){
            $query->where('usertype','=', 1)->orWhere('usertype','=', 2);
        } else {
            $query->where('usertype', '=', $userType);
        }
    }
    return $query->get()->toarray()[0]['total'];
}

mysql Query

SELECT SUM(sales.amount) as total FROM `sales`  INNER JOIN user
ON sales.userid = user.userid 
And sales.bankid = user.bank_id

WHERE sales.bankid=1
user.bank_id=1
AND sales.month=8
AND sales.year= 2020
And user.usertype=6

Another query(2) i have try, i did not get 'Allowed memory exhausted' issue here. it is working fine but when i try to get usertype 1 and usertype 2 records. i am getting wrong value.

function test($bank_id=NULL,$month=NULL,$year=NULL,$userType=NULL){
    $query = Sales::query();
    if($bank_id){
        $query = $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($userType!="all"){   
        $query->whereIn('userid', function ($query1) use ($userType,$bank_id) {
            $query1->select('userid')
                ->from('user');
                if($bank_id!="all"){
                    $query1 = $query1->where('bank_id', $bank_id);
                }
                if($userType==1){
                    $query1->where('usertype', 1)->orWhere('agentType', 2);
                } else {
                    $query1->where('usertype', $userType);
                }
        });
    }
    return $query->get()->toarray()[0]['total'];
}

mysql query

$query=DB::select("SELECT SUM(sales.amount) as total 
FROM `sales`
WHERE month=8
AND year= 2020
AND bank=2
AND `userid` IN (select userid from user where bank_id=2 AND(usertype=1 OR usertype=2))");

Thank you.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3gCv3NM
via IFTTT