Main feed queries
Closed this issue · 3 comments
Top questions/answers (sorted by a weird score):
SELECT 'question', id, (((select count(*) from question_rating where question_id = question.id and rate = 1) + 1.9208) / ((select count(*) from question_rating where question_id = question.id)) - 1.96 * SQRT(((select count(*) from question_rating where question_id = question.id and rate = 1) * (select count(*) from question_rating where question_id = question.id and rate = -1)) / ((select count(*) from question_rating where question_id = question.id)) + 0.9604) / ((select count(*) from question_rating where question_id = question.id))) / (1 + 3.8416 / ((select count(*) from question_rating where question_id = question.id))) as score FROM question WHERE (select count(*) from question_rating where question_id = question.id) > 0 union SELECT 'answer', id, (((select count(*) from answer_rating where answer_id = answer.id and rate = 1) + 1.9208) / ((select count(*) from answer_rating where answer_id = answer.id)) - 1.96 * SQRT(((select count(*) from answer_rating where answer_id = answer.id and rate = 1) * (select count(*) from answer_rating where answer_id = answer.id and rate = -1)) / ((select count(*) from answer_rating where answer_id = answer.id)) + 0.9604) / ((select count(*) from answer_rating where answer_id = answer.id))) / (1 + 3.8416 / ((select count(*) from answer_rating where answer_id = answer.id))) as score from answer where (select count(*) from answer_rating where answer_id = answer.id) > 0 ORDER BY score DESC;
New questions/answers (sorted by date):
select 'question', id, "date" from question union select 'answer', id, "date" from answer order by "date" desc;
Recommended questions/answers (sorted by the weird score, filter results to only show posts from people the member follows):
SELECT 'question', id, (((select count(*) from question_rating where question_id = question.id and rate = 1) + 1.9208) / ((select count(*) from question_rating where question_id = question.id)) - 1.96 * SQRT(((select count(*) from question_rating where question_id = question.id and rate = 1) * (select count(*) from question_rating where question_id = question.id and rate = -1)) / ((select count(*) from question_rating where question_id = question.id)) + 0.9604) / ((select count(*) from question_rating where question_id = question.id))) / (1 + 3.8416 / ((select count(*) from question_rating where question_id = question.id))) as score FROM question WHERE (select count(*) from question_rating where question_id = question.id) > 0 and question.author_id in (select following_id from follow_member where follower_id = $USER_ID) union SELECT 'answer', id, (((select count(*) from answer_rating where answer_id = answer.id and rate = 1) + 1.9208) / ((select count(*) from answer_rating where answer_id = answer.id)) - 1.96 * SQRT(((select count(*) from answer_rating where answer_id = answer.id and rate = 1) * (select count(*) from answer_rating where answer_id = answer.id and rate = -1)) / ((select count(*) from answer_rating where answer_id = answer.id)) + 0.9604) / ((select count(*) from answer_rating where answer_id = answer.id))) / (1 + 3.8416 / ((select count(*) from answer_rating where answer_id = answer.id))) as score from answer where (select count(*) from answer_rating where answer_id = answer.id) > 0 and answer.author_id in (select following_id from follow_member where follower_id = $USER_ID) ORDER BY score DESC;
Queries done at the back end!
select name from topic inner join question_topic on topic_id = id group by name order by count(*) desc limit 5
select from member order by score desc limit 5