当我用 laravel 编写连接查询时，它会'Allowed memory exhausted'

Question

我在laravel项目中编写了联接查询。数据库中有1M条记录。当我加载页面时，它显示"允许内存耗尽"消息。有人能帮我解决这个问题吗？我的代码：

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查询

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

另一个查询(2(我已经尝试过了，我在这里没有得到"允许内存耗尽"的问题。它运行良好，但当我尝试获取usertype1和usertype2记录时。我得到了错误的价值。

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=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))");

谢谢。

Answer 1

在网页顶部添加ini_set('memory_limit', '1024M');行。

Answer 2

数据库中有100万条销售记录和3万条用户记录。我已经在数据库中创建了带有用户和销售表的视图。我已经包含了amount、month、year、bank、userid和usertype表字段。现在加载问题没有发生。