Laravel 10 ORM：获取历史记录数小于计划目标的推广账户

第一步：确认模型关联定义 #

先确保Eloquent模型已正确定义表间关联：

PromotedAccount 模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

class PromotedAccount extends Model
{
    protected $table = 'promoted_accounts';

    // 关联所属的推广计划
    public function plan(): BelongsTo
    {
        return $this->belongsTo(PromotedAccountPlan::class, 'promoted_account_plan_id');
    }

    // 关联历史记录
    public function histories(): HasMany
    {
        return $this->hasMany(PromotedAccountHistory::class, 'promoted_account_id');
    }
}

PromotedAccountPlan 模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class PromotedAccountPlan extends Model
{
    protected $table = 'promoted_account_plans';

    public function accounts(): HasMany
    {
        return $this->hasMany(PromotedAccount::class, 'promoted_account_plan_id');
    }
}

PromotedAccountHistory 模型 #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class PromotedAccountHistory extends Model
{
    protected $table = 'promoted_account_histories';

    public function account(): BelongsTo
    {
        return $this->belongsTo(PromotedAccount::class, 'promoted_account_id');
    }
}

第二步：编写查询逻辑 #

要筛选出关联历史记录数 < 所属计划target值的账户，有两种主流实现方式：

方法一：Join关联 + withCount（性能更优） #

直接关联计划表并统计历史记录数，适合数据量较大的场景：

use App\Models\PromotedAccount;

$accounts = PromotedAccount::query()
    // 关联推广计划表获取target字段
    ->join('promoted_account_plans', 'promoted_accounts.promoted_account_plan_id', '=', 'promoted_account_plans.id')
    // 统计每个账户的历史记录总数
    ->withCount('histories')
    // 核心筛选条件：历史记录数 < 计划目标值
    ->whereRaw('promoted_account_histories_count < promoted_account_plans.target')
    // 可选：指定查询字段，避免冗余数据
    ->select('promoted_accounts.*', 'promoted_account_plans.target')
    ->get();

方法二：纯Eloquent子查询写法 #

更贴合ORM关联风格，适合逻辑清晰优先的场景：

use App\Models\PromotedAccount;
use Illuminate\Database\Eloquent\Builder;

$accounts = PromotedAccount::query()
    ->withCount('histories')
    // 通过子查询匹配计划的target值并做比较
    ->whereRaw('promoted_account_histories_count < (SELECT target FROM promoted_account_plans WHERE id = promoted_accounts.promoted_account_plan_id)')
    ->get();

补充说明 #

• withCount('histories')会自动生成promoted_account_histories_count字段，存储对应账户的历史记录总数
• 可根据业务需求追加额外筛选条件（如账户名称模糊匹配）或排序逻辑（如orderBy('promoted_account_histories_count', 'asc')）

内容的提问来源于stack exchange，提问作者Vinay SIngh