1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
(ns datalayer
(:require redis
tags)
(:use config
utils))
;;;; Message lookup
(defn recent-posts-query [user-id]
(format "
SELECT u.user_id, u.nick, u.avatar,
m.content, m.message_id%s
FROM users u
LEFT JOIN messages m on m.message_id =
(SELECT message_id FROM messages
WHERE user_id = u.user_id
AND is_image
AND room_id IN (SELECT room_id from rooms where admin_only = false)
ORDER BY created_on desc LIMIT 1)
WHERE u.user_id = ANY(?)"
(if user-id
(format
",
EXISTS (SELECT 1 FROM tags
WHERE tag = 'favorite' AND user_id = %s AND message_id = m.message_id) AS favorited"
user-id)
", false AS favorited")))
(defn lookup-recent-posts [user-tag-id user-ids]
(do-select [(recent-posts-query user-tag-id)
(sql-array "int" user-ids)]))
(defn lookup-recent-posts-tagless [user-tag-id user-ids]
(do-select [(recent-posts-query nil)
(sql-array "int" user-ids)]))
(defn fetch-message-by-id [m-id]
(let [query "SELECT m.message_id, m.content, m.created_on, m.user_id,
u.nick, u.avatar, r.key, r.admin_only
FROM messages m, users u, rooms r
WHERE m.user_id = u.user_id
AND r.room_id = m.room_id
AND m.message_id = ?"]
(first (do-select [query (maybe-parse-int m-id -1)]))))
;;;; Popular Posts
(def popular-dumps-qry "
select u.nick, u.avatar, r.key, m.message_id, m.content, m.created_on, count(*) as count,
array_agg(u2.nick) as user_nicks
from users u, messages m, rooms r, tags t, users u2
where lower(u.nick) = lower(?)
and u.user_id = m.user_id and m.message_id = t.message_id
and m.room_id = r.room_id and m.is_image = true and r.admin_only = false
and t.tag = 'favorite'
and t.user_id = u2.user_id
group by u.nick, u.avatar, r.key, m.message_id, m.content, m.created_on
order by count desc limit ? offset ?")
(defn fetch-popular-dumps [nick viewer-nick]
(for [d (do-select [popular-dumps-qry nick 40 0])]
(let [favers (vec (.getArray (:user_nicks d)))]
(assoc d
:favers favers
:favorited (some #(= % viewer-nick) favers)))))
(defn fetch-popular-dumps-redis [nick viewer-nick]
(let [rkey (str "popular:" nick)
msg-ids (redis/with-server redis-server
(redis/zrevrange rkey 0 (dec num-popular-dumps)))
msg-ids (map maybe-parse-int msg-ids)]
(tags/fetch-dumps-by-ids msg-ids viewer-nick)))
|
