def fetch_topics(post_id):
return fit(fetch("""SELECT t.topic_id, t.topic, t.description
FROM post_topic pt
JOIN topics t
ON pt.topic_id = t.topic_id
AND pt.post_id={}""".format(post_id)),
('topic_id', 'topic', 'description'))
def following(user_id):
return fit(fetch("""SELECT u2.user_id,u2.username,u2.bio
FROM followers f
JOIN users u2 ON f.follows_id=u2.user_id
AND f.follower_id={}
""".format(user_id)),
('user_id', 'username', 'bio'))
def fetch_all(user_id):
return fit(fetch("SELECT user_id,username,bio,join_date,"
"IF(f.follows_id IS NULL, 0, 1) AS follows "
"FROM users u "
"LEFT JOIN followers f "
"ON f.follower_id={} "
"AND f.follows_id=u.user_id".format(user_id)),
('user_id', 'username', 'bio', 'join_date', 'follows'))
def fetch_posts(topic_id):
return fit(fetch("""SELECT p.post_id,p.title,p.content,p.user_id
FROM topics t
JOIN post_topic pt
ON t.topic_id = pt.topic_id
AND t.topic_id={}
JOIN posts p
ON pt.post_id=p.post_id""".format(topic_id)),
('post_id', 'title', 'content', 'user_id'))
def create(fields):
return (execute("INSERT INTO posts(user_id, content, title, "
"description) "
"VALUES(%d, %s, %s, %s)" % (
int(fields['user_id']),
quote_string(fields['content']),
quote_string(fields['title']),
quote_string(fields['description']))),
fit(fetch("SELECT LAST_INSERT_ID()"), ('last_id',)))
def fetch_user_likes(post_id):
"""
Fetch users that like a given post
"""
return fit(fetch("""SELECT u.user_id,u.username
FROM user_likes_post ulp
JOIN users u
ON ulp.user_id=u.user_id
and ulp.post_id={}""".format(post_id)),
('user_id', 'username'))
def fetch_all(user_id):
return fit(fetch("SELECT p.post_id, content, created, p.user_id, "
"description, title, "
"IF(ulp.post_id IS NULL, 0, 1) as likedBy "
"FROM posts p "
"LEFT JOIN user_likes_post ulp "
"ON ulp.user_id={} "
"AND p.post_id=ulp.post_id".format(user_id)),
('post_id', 'content', 'created', 'user_id',
'description', 'title', 'likedBy'))
def fetch_like_posts(user_id):
"""
Fetch posts a user likes
"""
return fit(fetch("""SELECT p.post_id,p.title,p.content,p.user_id
FROM user_likes_post ulp
JOIN posts p
ON ulp.post_id = p.post_id
AND ulp.user_id={}""".format(user_id)),
('post_id', 'title', 'content', 'user_id'))
def get(self, user_id):
r = fit(fetch("""DROP TEMPORARY TABLE IF EXISTS user_counts;
CREATE TEMPORARY TABLE user_counts AS
SELECT u2.user_id,count(*) as cnt
FROM user_likes_post u1
JOIN user_likes_post u2
ON u1.user_id != u2.user_id AND u1.post_id = u2.post_id
WHERE u1.user_id={0}
GROUP BY u2.user_id;
DROP TEMPORARY TABLE IF EXISTS collab_score;
CREATE TEMPORARY TABLE collab_score AS (
SELECT u.post_id, sum(uc.cnt) AS score
FROM user_counts uc
JOIN (
SELECT user_id, post_id
FROM user_likes_post
WHERE post_id NOT IN (
SELECT post_id
FROM user_likes_post ulp
WHERE ulp.user_id = {0}
)
) u
ON u.user_id = uc.user_id
GROUP BY u.post_id
ORDER BY score DESC
);
DROP TEMPORARY TABLE IF EXISTS follow_score;
CREATE TEMPORARY TABLE follow_score AS (
SELECT post_id, COUNT(*) AS score
FROM followers f
JOIN users u ON f.follows_id=u.user_id AND f.follower_id={0}
JOIN user_likes_post ulp ON ulp.user_id=f.follows_id
WHERE post_id NOT IN (
SELECT post_id
FROM user_likes_post ulp2
WHERE ulp2.user_id={0}
)
GROUP BY post_id
ORDER BY COUNT(*) DESC
);
SELECT p.post_id, title, description, content, total_score FROM (
SELECT a.post_id, 10 * a.score + IFNULL(b.score, 0) AS total_score
FROM collab_score a
LEFT JOIN follow_score b
ON a.post_id=b.post_id
) c
JOIN posts p
ON c.post_id=p.post_id
ORDER BY total_score DESC;
""".format(user_id), multi=True),
('post_id', 'title', 'description', 'content',
'total_score'))
for row in r:
row['total_score'] = int(row['total_score'])
return r
def get(self):
args = parser.parse_args()
query = args['query']
words = [x.strip().lower() for x in query.split()]
words = list(set(words))
if len(words) == 0:
return []
tentative = [word for word in words if word not in stopwords]
if len(tentative) > 0:
words = tentative
word_values = ",".join(["({})".format(quote_string(_))
for _ in words])
non_pk_cols = ','.join(['title', 'description', 'content'])
r = fetch("""
DROP TEMPORARY TABLE IF EXISTS words;
DROP TEMPORARY TABLE IF EXISTS words2;
CREATE TEMPORARY TABLE words(word VARCHAR(40));
CREATE TEMPORARY TABLE words2(word VARCHAR(40));
INSERT INTO words VALUES {1};
INSERT INTO words2 VALUES {1};
SELECT post_id, {0}, MAX(tf_idf)
FROM (
SELECT post_id, {0}, word, LOG(((SELECT COUNT(*) FROM posts) + 1) / (doc_freq)) * tf as tf_idf
FROM (
SELECT p.post_id, {0}, w.word, IFNULL(doc_freq,0) as doc_freq,
ROUND((LENGTH(p.content) - LENGTH(REPLACE(LOWER(p.content), w.word, ""))) / LENGTH(w.word)) AS tf
FROM posts p
JOIN words w
LEFT JOIN (
SELECT word, COUNT(*) as doc_freq
FROM posts p, words2 w
WHERE LOCATE(w.word, LOWER(p.content)) > 0
GROUP BY word
) AS tf
ON w.word = tf.word
) as d
) AS a
GROUP BY post_id
ORDER BY MAX(tf_idf) DESC;""".format(non_pk_cols, word_values),
multi=True)
posts = fit(r, ('post_id', 'title', 'description', 'content', 'tf_idf'))
for post in posts:
post['topics'] = Post.fetch_topics(post['post_id'])
return posts
def search(query):
return fit(fetch(
"SELECT user_id, username FROM users WHERE username LIKE '%s%%'"
% escape_string(query)),
('user_id', 'username'))