gistfile1.txt

  def self.hot_objects(type, date)
  
    objects = Photo.find_by_sql("SELECT id, title, points_count, filename, parent_id, sum(score) as fame FROM (
SELECT photos.id, photos.title, photos.points_count, photos.filename, photos.parent_id,
COUNT(photos.id) * 5 as score
FROM `photos` LEFT JOIN comments ON comments.commentable_id = photos.id AND comments.commentable_type = 'Photo' AND photos.user_id != comments.user_id AND `photos`.`parent_id` IS NULL
WHERE (comments.created_at > '#{date}') GROUP BY photos.id
UNION
SELECT photos.id, photos.title, photos.points_count, photos.filename, photos.parent_id,
COUNT(photos.id) / 2 as score
FROM `photos` LEFT JOIN viewings ON viewings.viewed_id = photos.id AND viewings.viewed_type = 'Photo' AND `photos`.`parent_id` IS NULL
WHERE viewings.created_at > '#{date}' GROUP BY photos.id
UNION
SELECT photos.id, photos.title, photos.points_count, photos.filename, photos.parent_id,
COUNT(photos.id) * 10 as score
FROM `photos` LEFT JOIN awardings ON awardings.awardable_id = photos.id AND awardings.awardable_type = 'Photo' AND `photos`.`parent_id` IS NULL
WHERE awardings.created_at > '#{date}' GROUP BY photos.id
UNION
SELECT photos.id, photos.title, photos.points_count, photos.filename, photos.parent_id,
COUNT(photos.id) * 2 as score
FROM `photos` LEFT JOIN points ON points.pointable_id = photos.id AND points.pointable_type = 'Photo' AND `photos`.`parent_id` IS NULL
WHERE points.created_at > '#{date}' GROUP BY photos.id )
AS total GROUP BY id ORDER BY fame DESC;")
    return objects
    
  end