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()
...
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire