我正在laravel中重建一个项目，目前的问题是通过表别名和sql计算定义一个复杂的自引用hasmanythrough关系。
此关系应根据匹配标记的向下和查找相关商户。模型拥有的标签越多，它们之间的关联就越大。
到目前为止，还不错。在我以前的项目中，我只写了以下合适的sql查询：

SELECT source_merchant.id, target_merchant.id, COUNT(target_merchant.id) /
((
    (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = source_merchant.id) + 
    (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = target_merchant.id) 
) /2 ) as similarity 
FROM merchants source_merchant 
LEFT JOIN tagged source_merchant_tags ON (
    source_merchant.id = source_merchant_tags.model_id AND 
    source_merchant_tags.model = 'Merchant'
) 
INNER JOIN tagged target_merchant_tags ON (
    source_merchant_tags.tag_id = target_merchant_tags.tag_id 
    AND (source_merchant_tags.model = 'Merchant' AND target_merchant_tags.model = 'Merchant') 
    AND (source_merchant_tags.model_id != target_merchant_tags.model_id)
)  
LEFT JOIN merchants target_merchant ON (
    target_merchant_tags.model_id = target_merchant.id AND target_merchant_tags.model = 'Merchant'
) 
WHERE source_merchant.id = 2 
GROUP BY source_merchant.id, target_merchant.id 
ORDER BY similarity DESC
LIMIT 5

最好是抓到像这样的东西

public function related_merchants() {
    return $this->hasManyThroug(relations_stuff_i_cannot_imagine...)
        ->selectRaw("SELECT source_merchant.id, target_merchant.id, COUNT(target_merchant.id) /
        ((
            (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = source_merchant.id) + 
            (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = target_merchant.id) 
        ) /2 ) as similarity")
        ->groupBy('source_merchant.id', 'target_merchant.id ')
        ->orderBy('similarity')
        ->limit(5);
}

就是这样：-）不幸的是，我找不到一个解决方案，因为我不知道如何定义合适的关系参数 hasManyThrough() ...
编辑-尝试按建议构建laravel查询：

public function getRelatedMerchantsAttribute() {

    return $this->from('merchants AS source_merchant')
        ->selectRaw('source_merchant.id, target_merchant.id, COUNT(target_merchant.id) /
            ((
                (' . DB::table('tagged')->whereRaw("model = 'Merchant' AND model_id = source_merchant.id")->count() . ') +
                ' .  DB::table('tagged')->whereRaw("model = 'Merchant' AND model_id = target_merchant.id")->count() . ')
                /2 ) AS similarity')
        ->lefJoin('tagged AS source_merchant_tags', function ($join) {
            $join->on('source_merchant.id', '=', 'source_merchant_tags.model_id')
            ->on('source_merchant_tags.model', '=', 'Merchant');
        })
        ->join('tagged AS target_merchant_tags', function ($join) {
            $join->on('source_merchant_tags.tag_id', '=', 'target_merchant_tags.tag_id')
            ->on('source_merchant_tags.model', '=', 'Merchant')
            ->on('target_merchant_tags.model', '=', 'Merchant')
            ->on('source_merchant_tags.model_id', '!=', 'target_merchant_tags.model_id');
        })
        ->leftJoin('merchants AS target_merchant', function ($join) {
            $join->on('target_merchant_tags.model_id ', '=', 'target_merchant.id')
            ->on('target_merchant_tags.model', '=', 'Merchant');
        })
        ->whereRaw('source_merchant.id = ?', [ $this->id ])
        ->groupBy('source_merchant.id', 'target_merchant.id ')
        ->orderBy('similarity')
        ->limit(5)
        ->get();

}

此解决方案尚未工作，因为：

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'merchants.id'
in 'where clause' (SQL: select count(*) as aggregate from `tagged`
where model = 'Merchant' AND model_id = merchants.id)

编辑-使用此查询会产生以下laravel错误：
sqlstate[hy093]：无效的参数号。sql语句：

select source_merchant.id, target_merchant.id, 
COUNT(target_merchant.id) /
((
    (select COUNT(*) from `tagged` where `model` = Merchant and `model_id` = source_merchant.id) +
    (select COUNT(*) from `tagged` where `model` = Merchant and `model_id` = target_merchant.id)
) /2 ) AS similarity
from `merchants` as `source_merchant`
left join `tagged` as `source_merchant_tags` on 
    `source_merchant`.`id` = `source_merchant_tags`.`model_id`
    and `source_merchant_tags`.`model` = Merchant
inner join `tagged` as `target_merchant_tags` on 
    `source_merchant_tags`.`tag_id` = `target_merchant_tags`.`tag_id` 
    and `source_merchant_tags`.`model` = Merchant
    and `target_merchant_tags`.`model` = 2
    and `source_merchant_tags`.`model_id` != `target_merchant_tags`.`model_id` 
left join `merchants` as `target_merchant` on 
    `target_merchant_tags`.`model_id` = `target_merchant`.`id`
    and `target_merchant_tags`.`model` = ?
where `source_merchant`.`id` = ? 
group by `source_merchant`.`id`, `target_merchant`.`id`
order by `similarity` desc
limit 5

我甚至想知道为什么这里使用当前的模型id： and target_merchant_tags.model = 2 ...
看看这个截图。条件参数“merchant”成为当前模型id，在本例中为2（所选文本）。红色圆圈中的两个参数保持为空。这里怎么了？