Skip to content

Instantly share code, notes, and snippets.

@sealabcore

sealabcore/gist:426044

Created June 4, 2010 22:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sealabcore/426044 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sealabcore/426044 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sealabcore created this gist Jun 4, 2010.
    26 changes: 26 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    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