Laravel5: Complex hasManyThrouh relation in model (with aliases and SQL calculations)

I am rebuilding a project in laravel and my current problem is to define a complex hasManyThrough relation with table aliasing and SQL calculations.

This relation should find related merchants depending on a downward sum of matching tags. The more same tags the models have, the more related they are.

So far, so good. In my old project, I just wrote down the following suitable SQL query:

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

Best would be to catch something like

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);
}

That's it :-) Unfortunally, I can't find a solution because I do not know, how to define suitable relation parameters in hasManyThrough()...

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2r0lDnx
via IFTTT