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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
|
import datetime
import logging
from django.contrib.auth.models import User, UserManager
from django.db import connection, models, transaction
from django.db.models import Q
from forum.models import *
from urllib import quote, unquote
class QuestionManager(models.Manager):
def get_translation_questions(self, orderby, page_size):
questions = self.filter(deleted=False, author__id__in=[28,29]).order_by(orderby)[:page_size]
return questions
def get_questions_by_pagesize(self, orderby, page_size):
questions = self.filter(deleted=False).order_by(orderby)[:page_size]
return questions
def get_questions_by_tag(self, tagname, orderby):
questions = self.filter(deleted=False, tags__name = unquote(tagname)).order_by(orderby)
return questions
def get_unanswered_questions(self, orderby):
questions = self.filter(deleted=False, answer_accepted=False).order_by(orderby)
return questions
def get_questions(self, orderby):
questions = self.filter(deleted=False).order_by(orderby)
return questions
def update_tags(self, question, tagnames, user):
"""
Updates Tag associations for a question to match the given
tagname string.
Returns ``True`` if tag usage counts were updated as a result,
``False`` otherwise.
"""
from forum.models import Tag
current_tags = list(question.tags.all())
current_tagnames = set(t.name for t in current_tags)
updated_tagnames = set(t for t in tagnames.split(' ') if t)
modified_tags = []
removed_tags = [t for t in current_tags
if t.name not in updated_tagnames]
if removed_tags:
modified_tags.extend(removed_tags)
question.tags.remove(*removed_tags)
added_tagnames = updated_tagnames - current_tagnames
if added_tagnames:
added_tags = Tag.objects.get_or_create_multiple(added_tagnames,
user)
modified_tags.extend(added_tags)
question.tags.add(*added_tags)
if modified_tags:
Tag.objects.update_use_counts(modified_tags)
return True
return False
def update_answer_count(self, question):
"""
Executes an UPDATE query to update denormalised data with the
number of answers the given question has.
"""
# for some reasons, this Answer class failed to be imported,
# although we have imported all classes from models on top.
from forum.models import Answer
self.filter(id=question.id).update(
answer_count=Answer.objects.get_answers_from_question(question).filter(deleted=False).count())
def update_view_count(self, question):
"""
update counter+1 when user browse question page
"""
self.filter(id=question.id).update(view_count = question.view_count + 1)
def update_favorite_count(self, question):
"""
update favourite_count for given question
"""
from forum.models import FavoriteQuestion
self.filter(id=question.id).update(favourite_count = FavoriteQuestion.objects.filter(question=question).count())
def get_similar_questions(self, question):
"""
Get 10 similar questions for given one.
This will search the same tag list for give question(by exactly same string) first.
Questions with the individual tags will be added to list if above questions are not full.
"""
#print datetime.datetime.now()
from forum.models import Question
questions = list(Question.objects.filter(tagnames = question.tagnames, deleted=False).all())
tags_list = question.tags.all()
for tag in tags_list:
extend_questions = Question.objects.filter(tags__id = tag.id, deleted=False)[:50]
for item in extend_questions:
if item not in questions and len(questions) < 10:
questions.append(item)
#print datetime.datetime.now()
return questions
class TagManager(models.Manager):
UPDATE_USED_COUNTS_QUERY = (
'UPDATE tag '
'SET used_count = ('
'SELECT COUNT(*) FROM question_tags '
'INNER JOIN question ON question_id=question.id '
'WHERE tag_id = tag.id AND question.deleted=0'
') '
'WHERE id IN (%s)')
def get_valid_tags(self, page_size):
from forum.models import Tag
tags = Tag.objects.all().filter(deleted=False).exclude(used_count=0).order_by("-id")[:page_size]
return tags
def get_or_create_multiple(self, names, user):
"""
Fetches a list of Tags with the given names, creating any Tags
which don't exist when necesssary.
"""
tags = list(self.filter(name__in=names))
#Set all these tag visible
for tag in tags:
if tag.deleted:
tag.deleted = False
tag.deleted_by = None
tag.deleted_at = None
tag.save()
if len(tags) < len(names):
existing_names = set(tag.name for tag in tags)
new_names = [name for name in names if name not in existing_names]
tags.extend([self.create(name=name, created_by=user)
for name in new_names if self.filter(name=name).count() == 0 and len(name.strip()) > 0])
return tags
def update_use_counts(self, tags):
"""Updates the given Tags with their current use counts."""
if not tags:
return
cursor = connection.cursor()
query = self.UPDATE_USED_COUNTS_QUERY % ','.join(['%s'] * len(tags))
cursor.execute(query, [tag.id for tag in tags])
transaction.commit_unless_managed()
def get_tags_by_questions(self, questions):
question_ids = []
for question in questions:
question_ids.append(question.id)
question_ids_str = ','.join([str(id) for id in question_ids])
related_tags = self.extra(
tables=['tag', 'question_tags'],
where=["tag.id = question_tags.tag_id AND question_tags.question_id IN (" + question_ids_str + ")"]
).distinct()
return related_tags
class AnswerManager(models.Manager):
GET_ANSWERS_FROM_USER_QUESTIONS = u'SELECT answer.* FROM answer INNER JOIN question ON answer.question_id = question.id WHERE question.author_id =%s AND answer.author_id <> %s'
def get_answers_from_question(self, question, user=None):
"""
Retrieves visibile answers for the given question. Delete answers
are only visibile to the person who deleted them.
"""
if user is None or not user.is_authenticated():
return self.filter(question=question, deleted=False)
else:
return self.filter(Q(question=question),
Q(deleted=False) | Q(deleted_by=user))
def get_answers_from_questions(self, user_id):
"""
Retrieves visibile answers for the given question. Which are not included own answers
"""
cursor = connection.cursor()
cursor.execute(self.GET_ANSWERS_FROM_USER_QUESTIONS, [user_id, user_id])
return cursor.fetchall()
class VoteManager(models.Manager):
COUNT_UP_VOTE_BY_USER = "SELECT count(*) FROM vote WHERE user_id = %s AND vote = 1"
COUNT_DOWN_VOTE_BY_USER = "SELECT count(*) FROM vote WHERE user_id = %s AND vote = -1"
COUNT_VOTES_PER_DAY_BY_USER = "SELECT COUNT(*) FROM vote WHERE user_id = %s AND DATE(voted_at) = DATE(NOW())"
def get_up_vote_count_from_user(self, user):
if user is not None:
cursor = connection.cursor()
cursor.execute(self.COUNT_UP_VOTE_BY_USER, [user.id])
row = cursor.fetchone()
return row[0]
else:
return 0
def get_down_vote_count_from_user(self, user):
if user is not None:
cursor = connection.cursor()
cursor.execute(self.COUNT_DOWN_VOTE_BY_USER, [user.id])
row = cursor.fetchone()
return row[0]
else:
return 0
def get_votes_count_today_from_user(self, user):
if user is not None:
cursor = connection.cursor()
cursor.execute(self.COUNT_VOTES_PER_DAY_BY_USER, [user.id])
row = cursor.fetchone()
return row[0]
else:
return 0
class FlaggedItemManager(models.Manager):
COUNT_FLAGS_PER_DAY_BY_USER = "SELECT COUNT(*) FROM flagged_item WHERE user_id = %s AND DATE(flagged_at) = DATE(NOW())"
def get_flagged_items_count_today(self, user):
if user is not None:
cursor = connection.cursor()
cursor.execute(self.COUNT_FLAGS_PER_DAY_BY_USER, [user.id])
row = cursor.fetchone()
return row[0]
else:
return 0
class ReputeManager(models.Manager):
COUNT_REPUTATION_PER_DAY_BY_USER = "SELECT SUM(positive)+SUM(negative) FROM repute WHERE user_id = %s AND (reputation_type=1 OR reputation_type=-8) AND DATE(reputed_at) = DATE(NOW())"
def get_reputation_by_upvoted_today(self, user):
"""
For one user in one day, he can only earn rep till certain score (ep. +200)
by upvoted(also substracted from upvoted canceled). This is because we need
to prohibit gaming system by upvoting/cancel again and again.
"""
if user is not None:
cursor = connection.cursor()
cursor.execute(self.COUNT_REPUTATION_PER_DAY_BY_USER, [user.id])
row = cursor.fetchone()
return row[0]
else:
return 0
class AwardManager(models.Manager):
def get_recent_awards(self):
awards = super(AwardManager, self).extra(
select={'badge_id': 'badge.id', 'badge_name':'badge.name',
'badge_description': 'badge.description', 'badge_type': 'badge.type',
'user_id': 'auth_user.id', 'user_name': 'auth_user.username'
},
tables=['award', 'badge', 'auth_user'],
order_by=['-awarded_at'],
where=['auth_user.id=award.user_id AND badge_id=badge.id'],
).values('badge_id', 'badge_name', 'badge_description', 'badge_type', 'user_id', 'user_name')
return awards
|
