Guest User

Untitled

a guest
Apr 26th, 2018
91
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.54 KB | None | 0 0
  1. SELECT source_merchant.id, target_merchant.id, COUNT(target_merchant.id) /
  2. ((
  3. (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = source_merchant.id) +
  4. (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = target_merchant.id)
  5. ) /2 ) as similarity
  6. FROM merchants source_merchant
  7. LEFT JOIN tagged source_merchant_tags ON (
  8. source_merchant.id = source_merchant_tags.model_id AND
  9. source_merchant_tags.model = 'Merchant'
  10. )
  11. INNER JOIN tagged target_merchant_tags ON (
  12. source_merchant_tags.tag_id = target_merchant_tags.tag_id
  13. AND (source_merchant_tags.model = 'Merchant' AND target_merchant_tags.model = 'Merchant')
  14. AND (source_merchant_tags.model_id != target_merchant_tags.model_id)
  15. )
  16. LEFT JOIN merchants target_merchant ON (
  17. target_merchant_tags.model_id = target_merchant.id AND target_merchant_tags.model = 'Merchant'
  18. )
  19. WHERE source_merchant.id = 2
  20. GROUP BY source_merchant.id, target_merchant.id
  21. ORDER BY similarity DESC
  22. LIMIT 5
  23.  
  24. public function related_merchants() {
  25. return $this->hasManyThroug(relations_stuff_i_cannot_imagine...)
  26. ->selectRaw("SELECT source_merchant.id, target_merchant.id, COUNT(target_merchant.id) /
  27. ((
  28. (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = source_merchant.id) +
  29. (SELECT COUNT(*) FROM tagged WHERE model = 'Merchant' AND model_id = target_merchant.id)
  30. ) /2 ) as similarity")
  31. ->groupBy('source_merchant.id', 'target_merchant.id ')
  32. ->orderBy('similarity')
  33. ->limit(5);
  34. }
Add Comment
Please, Sign In to add comment