Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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);
- }
Add Comment
Please, Sign In to add comment