Using pidge

forum_post.clj

(ns huddler.sql.forum-post
  (:use [huddler.util.sql :as sql]
        [pidge.sort]
        [pidge.store.redis]))

;                    site type tid order
(def post-key-format "%s-%d-%d-id-asc")

(defn map-posts-by-id-desc [thread_id f]
  (sql/query-map f "SELECT thread_id, id, 
                   rank() OVER (partition by thread_id order by post_timestamp desc, id desc) AS score
                   FROM forum_posts_info  
                   WHERE (status & get_huddler_constant('STATUS_BIT_VIEWABLE')::int)::bool AND 
                   thread_id = ? order by post_timestamp, id" thread_id))
  
(extend-type clojure.lang.APersistentMap
                          Sortable
                          (ident [this] (get this :id))
                          (score [this] (get this :score)))

(defn container-key [domain thread_id]
  (format post-key-format domain 35 thread_id))

; (fp/display-order-import "headfi" 253245)
; "Elapsed time: 1917.79 msecs"
(defn display-order-import [domain thread_id]
  (let [c (new-container (container-key domain thread_id))]
    (update c (fn [c]
                (sql/with-conn (map-posts-by-id-desc 
                                 thread_id
                                 (fn [r]
                                   (add c r))))))))

(defn display-order-page-sql [ids scores]
  (sql/with-conn
    (sql/query (format "WITH display_order AS (
                          SELECT UNNEST(ARRAY[%s]) AS id,
                                 UNNEST(ARRAY[%s]) AS pos
                        )
                        SELECT * FROM display_order
                        JOIN forum_posts ON (
                            display_order.id = forum_posts.id
                        )
                        ORDER BY display_order.pos DESC", 
                       (clojure.string/join "," ids),
                       (clojure.string/join "," scores)))))


; (time (fp/display-order-page "headfi" 253245 0 14))
; "Elapsed time: 28.094 msecs"
(defn display-order-page [domain thread_id page_no page_size]
  (let [c (new-container (container-key domain thread_id))
        objects (with c (fn [c] (page c (* page_no page_size) page_size)))
        ids (map #(ident %) objects)
        scores (map #(score %) objects)]
    (display-order-page-sql ids scores)))