多对多关联SQL查询：筛选符合共同房产条件的代理及Laravel实现

表结构说明 #

现有三张表实现代理与房产的多对多关联：

• agent表：仅含id主键字段，现有id为1、2、3、4、5共5条代理数据
• properties表：仅含id主键字段，现有id为1到6共6条房产数据
• agent_properties中间表：存储agent_id与property_id的绑定关系，用于关联代理和房产

查询规则 #

需查询满足以下条件的代理：该代理和至少两个不同的其他代理，分别共同拥有至少2个重叠的关联房产。
测试场景下预期返回结果：代理1、代理3、代理5 #

实现方案 #

原生SQL实现 #

SELECT DISTINCT ap1.agent_id
FROM agent_properties ap1
INNER JOIN agent_properties ap2 
    ON ap1.property_id = ap2.property_id 
    AND ap1.agent_id <> ap2.agent_id
-- 按代理对分组统计共同房产数量
GROUP BY ap1.agent_id, ap2.agent_id
HAVING COUNT(DISTINCT ap1.property_id) >= 2
-- 按主代理分组统计符合要求的其他代理数量
GROUP BY ap1.agent_id
HAVING COUNT(DISTINCT ap2.agent_id) >= 2;

逻辑说明：通过中间表自关联匹配所有共享房产的不同代理对，先筛选出共同房产≥2的代理对，再统计每个代理对应的符合要求的其他代理数量，取数量≥2的代理即为最终结果。

Laravel Eloquent实现 #

首先需要提前在Agent模型中定义多对多关联：

// app/Models/Agent.php
namespace App\Models;

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

class Agent extends Model
{
    // 若表名非Laravel默认的agents，需手动指定
    protected $table = 'agent';

    public function properties(): BelongsToMany
    {
        return $this->belongsToMany(Property::class, 'agent_properties');
    }
}

查询代码：

use App\Models\Agent;

$qualifiedAgentIds = Agent::query()
    ->select('agent.id')
    ->join('agent_properties as ap1', 'agent.id', '=', 'ap1.agent_id')
    ->join('agent_properties as ap2', function ($join) {
        $join->on('ap1.property_id', '=', 'ap2.property_id')
            ->whereColumn('ap1.agent_id', '!=', 'ap2.agent_id');
    })
    ->groupBy('ap1.agent_id', 'ap2.agent_id')
    ->havingRaw('COUNT(DISTINCT ap1.property_id) >= 2')
    ->groupBy('ap1.agent_id')
    ->havingRaw('COUNT(DISTINCT ap2.agent_id) >= 2')
    ->pluck('id');
// 输出结果包含1、3、5三个代理id

内容的提问来源于stack exchange，提问作者Tony Sala