php - SQL记录检索

Question

美好的一天社区，

我需要帮助从 mysql 表中检索记录，如下所示。请注意我的目标结果:

下面是我目前的方法，它在少量行上运行良好，但在处理数千甚至数百行时非常慢或耗时。

public function usersTopRecruits(){
    $tempUID = array();
    $mainAssocArr = array();
    for ($i=0; $i < User::count(); $i++) { 
        if($i == 0){
            $offset = 0;
        }
        else{           
            $offset = $this->take * $i;
        }

        $users = User::take($this->take)->offset($offset)->get();

        foreach ($users as $value) {
            $refCnts = User::where('referral', $value->aff_id)->count();

            if($refCnts > 0){
                $tempUID['userAffID'] = $value->aff_id;
                $tempUID['userrecruits'] = $refCnts;
                $mainAssocArr[] = $tempUID;
            }
        }
    }//End of For loop

    //Here, i tried to sort by highest recruits

    foreach ($mainAssocArr as $key => $row) {
        $desc[$key]  = $row['userrecruits'];
        $asc[$key] = $row['userAffID'];
    }

    array_multisort($desc, SORT_DESC, $asc, SORT_ASC, $mainAssocArr);

    return $mainAssocArr;
}

当前月份:

public function userTopRecruitsCurrMonth(){
    $tempUID = array();
    $mainAssocArr = array();
    for ($i=0; $i < User::count(); $i++) { 
        if($i == 0){
            $offset = 0;
        }
        else{           
            $offset = $this->take * $i;
        }

        $users = User::take($this->take)->offset($offset)->get();

        foreach ($users as $value) {
            $refCnts = User::where('referral', $value->aff_id)->where('signup_date', '>=', 'startDateOfCurrentMonth')->count();

            if($refCnts > 0){
                $tempUID['userAffID'] = $value->aff_id;
                $tempUID['userRecruits'] = $refCnts;
                $mainAssocArr[] = $tempUID;
            }
        }
    }//End of For loop


    foreach ($mainAssocArr as $key => $row) {
        $volume[$key]  = $row['userRecruits'];
        $edition[$key] = $row['userAffID'];
    }

    array_multisort($volume, SORT_DESC, $edition, SORT_ASC, $mainAssocArr);

    return $mainAssocArr;
}

如有任何帮助或建议，我们将不胜感激。

注意:我使用的是 Eloquent 模型，但没有使用“laravel”

Answer 1

根据您的评论，我了解到为您提供 SQL 查询可能会有所帮助。

这个简单的聚合查询将返回出现次数最多(所有时间)的 REFERRAL，最高的排在最前面。

SELECT referral, COUNT(*) 
FROM table
GROUP BY referral
ORDER BY COUNT(*) DESC

如果您要查找当月的相同结果，则只需添加一个 WHERE 子句:

SELECT referral, COUNT(*) 
FROM table
WHERE YEAR(sigmup_date) = YEAR(NOW()) AND MONTH(sigmup_date) = MONTH(NOW()) 
GROUP BY referral
ORDER BY COUNT(*) DESC