Laravel 10 跨表字段groupBy查询实现及性能优化需求

针对你的需求，这里提供单查询、高性能的实现方式，核心是利用内连接过滤无效数据，同时按指定字段分组：

核心查询代码 #

直接使用DB门面构建单SQL语句查询：

$filterOptions = DB::table('homes')
    // 内连接中间表，自动排除无字段数据的房屋
    ->join('home_fields', 'homes.id', '=', 'home_fields.home_id')
    // 内连接字段表，确保关联有效字段
    ->join('fields', 'home_fields.field_id', '=', 'fields.id')
    ->select(
        'homes.home_type',
        'fields.field_name',
        // 统计该分组下的房屋数量（可选，根据前端需求调整）
        DB::raw('COUNT(DISTINCT homes.id) as house_count')
    )
    // 按指定的两个字段分组
    ->groupBy('homes.home_type', 'fields.field_name')
    ->get();

关键说明 #

• 过滤无效数据：使用inner join而非left join，自动剔除没有关联字段数据的房屋和字段，无需额外where条件
• 分组规范：严格按照home_type和field_name分组，符合Laravel严格模式要求（若开启strict模式，select中的非聚合字段必须在groupBy中）
• 去重统计：用COUNT(DISTINCT homes.id)避免同一房屋被重复统计（如果存在一个房屋对应同一字段多条记录的场景）

性能优化建议 #

为了处理大量数据，必须给相关字段添加索引：

• 给home_fields表的home_id和field_id添加联合索引：CREATE INDEX idx_home_fields_home_field ON home_fields(home_id, field_id);
• 给homes表的home_type添加单列索引：CREATE INDEX idx_homes_home_type ON homes(home_type);
• 给fields表的field_name添加单列索引：CREATE INDEX idx_fields_field_name ON fields(field_name);

模型关联方式（可选） #

如果习惯用Eloquent模型查询，先在Home模型定义关联：

// app/Models/Home.php
public function fields()
{
    return $this->belongsToMany(Field::class, 'home_fields')
        ->withPivot('value');
}

然后通过模型构建查询：

$filterOptions = Home::query()
    ->has('fields') // 确保房屋有关联字段数据
    ->join('fields', 'home_fields.field_id', '=', 'fields.id')
    ->select(
        'homes.home_type',
        'fields.field_name',
        DB::raw('COUNT(DISTINCT homes.id) as house_count')
    )
    ->groupBy('homes.home_type', 'fields.field_name')
    ->get();

内容的提问来源于stack exchange，提问作者0x9342812j1o