def user_vote_change_comments(period=None, user=None):
rel = Vote.rel(Account, Comment)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, comment_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
amount = sa.cast(rt.c.name, sa.Integer)
cols = [
author_dt.c.value,
sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
]
query = sa.and_(
author_dt.c.thing_id == rt.c.rel_id, author_dt.c.key == "author_id", comment_tt.c.thing_id == rt.c.thing2_id
)
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.extend((rt.c.date >= earliest, comment_tt.c.date >= earliest))
if user is not None:
query.clauses.append(author_dt.c.value == str(user._id))
s = sa.select(cols, query, group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def user_vote_change_links(period=None, user=None):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, link_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
link_dt = tdb.types_id[Link._type_id].data_table[0].alias(aliases.next())
# Create an SQL CASE statement for the subreddit vote multiplier
cases = []
for subreddit in subreddits_with_custom_karma_multiplier():
cases.append( (sa.cast(link_dt.c.value,sa.Integer) == subreddit._id,
subreddit.post_karma_multiplier) )
cases.append( (True, g.post_karma_multiplier) ) # The default article multiplier
query = sa.and_(author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
link_tt.c.thing_id == rt.c.thing2_id,
link_dt.c.key == 'sr_id',
link_dt.c.thing_id == rt.c.thing2_id)
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.extend((rt.c.date >= earliest, link_tt.c.date >= earliest))
if user is not None:
query.clauses.append(author_dt.c.value == str(user._id))
s = sa.select([author_dt.c.value, sa.func.sum(sa.cast(rt.c.name, sa.Integer) * sa.case(cases))],
query, group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def top_users():
type = tdb.types_id[Account._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
karma = dt.alias(aliases.next())
s = sa.select(
[tt.c.thing_id],
sa.and_(
tt.c.spam == False, tt.c.deleted == False, karma.c.thing_id == tt.c.thing_id, karma.c.key.like("%_karma")
),
group_by=[tt.c.thing_id],
order_by=sa.desc(sa.func.sum(sa.cast(karma.c.value, sa.Integer))),
limit=10,
)
# Translation of query:
# SELECT
# reddit_thing_account.thing_id,
# WHERE
# (reddit_thing_account.spam = f AND
# reddit_thing_account.deleted = f AND
# reddit_thing_account.thing_id = reddit_data_account.thing_id AND
# reddit_data_account.key LIKE '%link_karma')
# ORDER BY
# sum(CAST(reddit_data_acc_3355.value AS INTEGER)) DESC
# LIMIT 10
rows = s.execute().fetchall()
return [r.thing_id for r in rows]
def user_karma_adjustments(period=None, user=None):
acct_info = tdb.types_id[Account._type_id]
acct_thing, acct_data = acct_info.thing_table, acct_info.data_table[0]
adj_info = tdb.types_id[KarmaAdjustment._type_id]
adj_thing, adj_data = adj_info.thing_table, adj_info.data_table[0]
aliases = tdb.alias_generator()
adj_data_2 = adj_data.alias(aliases.next())
amount = sa.cast(adj_data_2.c.value, sa.Integer)
cols = [
adj_data.c.value,
sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
]
query = sa.and_(adj_data.c.thing_id == adj_thing.c.thing_id,
adj_data.c.key == 'account_id',
adj_data.c.thing_id == adj_data_2.c.thing_id,
adj_data_2.c.key == 'amount')
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.append(adj_thing.c.date >= earliest)
if user is not None:
query.clauses.append(adj_data.c.value == str(user._id))
s = sa.select(cols, query, group_by=adj_data.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def user_vote_change_comments(period=None, user=None):
rel = Vote.rel(Account, Comment)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, comment_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
amount = sa.cast(rt.c.name, sa.Integer)
cols = [
author_dt.c.value,
sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
]
query = sa.and_(author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
comment_tt.c.thing_id == rt.c.thing2_id)
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.extend(
(rt.c.date >= earliest, comment_tt.c.date >= earliest))
if user is not None:
query.clauses.append(author_dt.c.value == str(user._id))
s = sa.select(cols, query, group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def user_vote_change_links(period = '1 day'):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, link_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
link_dt = tdb.types_id[Link._type_id].data_table[0].alias(aliases.next())
# Create an SQL CASE statement for the subreddit vote multiplier
cases = []
for subreddit in subreddits_with_custom_karma_multiplier():
cases.append( (sa.cast(link_dt.c.value,sa.Integer) == subreddit._id,
subreddit.post_karma_multiplier) )
cases.append( (True, g.post_karma_multiplier) ) # The default article multiplier
date = utils.timeago(period)
s = sa.select([author_dt.c.value, sa.func.sum(sa.cast(rt.c.name, sa.Integer) * sa.case(cases))],
sa.and_(rt.c.date >= date,
author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
link_tt.c.thing_id == rt.c.thing2_id,
link_tt.c.date >= date,
link_dt.c.key == 'sr_id',
link_dt.c.thing_id == rt.c.thing2_id),
group_by = author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def user_karma_adjustments(period=None, user=None):
acct_info = tdb.types_id[Account._type_id]
acct_thing, acct_data = acct_info.thing_table, acct_info.data_table[0]
adj_info = tdb.types_id[KarmaAdjustment._type_id]
adj_thing, adj_data = adj_info.thing_table, adj_info.data_table[0]
aliases = tdb.alias_generator()
adj_data_2 = adj_data.alias(aliases.next())
amount = sa.cast(adj_data_2.c.value, sa.Integer)
cols = [
adj_data.c.value,
sa.func.sum(sa.case([(amount > 0, amount)], else_=0)),
sa.func.sum(sa.case([(amount < 0, amount * -1)], else_=0)),
]
query = sa.and_(
adj_data.c.thing_id == adj_thing.c.thing_id,
adj_data.c.key == "account_id",
adj_data.c.thing_id == adj_data_2.c.thing_id,
adj_data_2.c.key == "amount",
)
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.append(adj_thing.c.date >= earliest)
if user is not None:
query.clauses.append(adj_data.c.value == str(user._id))
s = sa.select(cols, query, group_by=adj_data.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def top_users():
type = tdb.types_id[Account._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
karma = dt.alias(aliases.next())
cases = [
(karma.c.key.like('%_link_karma'),
sa.cast(karma.c.value, sa.Integer) * g.post_karma_multiplier)
]
for subreddit in subreddits_with_custom_karma_multiplier():
key = "%s_link_karma" % subreddit.name
cases.insert(0, (karma.c.key == key,
sa.cast(karma.c.value, sa.Integer) * subreddit.post_karma_multiplier))
s = sa.select(
[tt.c.thing_id],
sa.and_(tt.c.spam == False,
tt.c.deleted == False,
karma.c.thing_id == tt.c.thing_id,
karma.c.key.like('%_karma')),
group_by = [tt.c.thing_id],
order_by = sa.desc(sa.func.sum(
sa.case(cases, else_ = sa.cast(karma.c.value, sa.Integer))
)),
limit = 10)
# Translation of query:
# SELECT
# reddit_thing_account.thing_id
# FROM
# reddit_thing_account,
# reddit_data_account
# WHERE
# (reddit_thing_account.spam = 'f' AND
# reddit_thing_account.deleted = 'f' AND
# reddit_thing_account.thing_id = reddit_data_account.thing_id AND
# reddit_data_account.key LIKE '%_karma')
# GROUP BY
# reddit_thing_account.thing_id
# ORDER BY
# sum(
# CASE
# WHEN reddit_data_account.key = 'lesswrong_link_karma' THEN
# CAST(reddit_data_account.value AS INTEGER) * 10
# ELSE CAST(reddit_data_account.value AS INTEGER)
# END
# ) DESC
# LIMIT 10
rows = s.execute().fetchall()
return [r.thing_id for r in rows]
def top_users():
type = tdb.types_id[Account._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
karma = dt.alias(aliases.next())
cases = [(karma.c.key.like('%_link_karma'),
sa.cast(karma.c.value, sa.Integer) * g.post_karma_multiplier)]
for subreddit in subreddits_with_custom_karma_multiplier():
key = "%s_link_karma" % subreddit.name
cases.insert(0,
(karma.c.key == key, sa.cast(karma.c.value, sa.Integer) *
subreddit.post_karma_multiplier))
s = sa.select([tt.c.thing_id],
sa.and_(tt.c.spam == False, tt.c.deleted == False,
karma.c.thing_id == tt.c.thing_id,
karma.c.key.like('%_karma')),
group_by=[tt.c.thing_id],
order_by=sa.desc(
sa.func.sum(
sa.case(cases,
else_=sa.cast(karma.c.value, sa.Integer)))),
limit=10)
# Translation of query:
# SELECT
# reddit_thing_account.thing_id
# FROM
# reddit_thing_account,
# reddit_data_account
# WHERE
# (reddit_thing_account.spam = 'f' AND
# reddit_thing_account.deleted = 'f' AND
# reddit_thing_account.thing_id = reddit_data_account.thing_id AND
# reddit_data_account.key LIKE '%_karma')
# GROUP BY
# reddit_thing_account.thing_id
# ORDER BY
# sum(
# CASE
# WHEN reddit_data_account.key = 'lesswrong_link_karma' THEN
# CAST(reddit_data_account.value AS INTEGER) * 10
# ELSE CAST(reddit_data_account.value AS INTEGER)
# END
# ) DESC
# LIMIT 10
rows = s.execute().fetchall()
return [r.thing_id for r in rows]
def user_vote_change_links(period=None, user=None):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, link_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
link_dt = tdb.types_id[Link._type_id].data_table[0].alias(aliases.next())
# Create an SQL CASE statement for the subreddit vote multiplier
cases = []
for subreddit in subreddits_with_custom_karma_multiplier():
cases.append((sa.cast(link_dt.c.value, sa.Integer) == subreddit._id,
subreddit.post_karma_multiplier))
cases.append(
(True, g.post_karma_multiplier)) # The default article multiplier
weight_cases = sa.case(cases)
amount = sa.cast(rt.c.name, sa.Integer)
cols = [
author_dt.c.value,
sa.func.sum(sa.case([(amount > 0, amount * weight_cases)], else_=0)),
sa.func.sum(
sa.case([(amount < 0, amount * -1 * weight_cases)], else_=0)),
]
query = sa.and_(author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
link_tt.c.thing_id == rt.c.thing2_id,
link_dt.c.key == 'sr_id',
link_dt.c.thing_id == rt.c.thing2_id)
if period is not None:
# Note: limiting this to only recent items is intentional
# http://lesswrong.com/r/discussion/lw/ghy/minor_separate_upvotes_and_downvotes_implimented/8d7f
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.extend(
(rt.c.date >= earliest, link_tt.c.date >= earliest))
if user is not None:
query.clauses.append(author_dt.c.value == str(user._id))
s = sa.select(cols, query, group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def subreddits_with_custom_karma_multiplier():
type = tdb.types_id[Subreddit._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
karma = dt.alias(aliases.next())
q = sa.select(
[tt.c.thing_id],
sa.and_(tt.c.spam == False, tt.c.deleted == False,
karma.c.thing_id == tt.c.thing_id,
karma.c.key == 'post_karma_multiplier'),
group_by=[tt.c.thing_id],
)
sr_ids = [r.thing_id for r in q.execute().fetchall()]
return Subreddit._byID(sr_ids, True, return_dict=False)
def top_users():
type = tdb.types_id[Account._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
account_data = dt.alias(aliases.next())
s = sa.select([tt.c.thing_id],
sa.and_(tt.c.spam == False, tt.c.deleted == False,
account_data.c.thing_id == tt.c.thing_id,
account_data.c.key.like('karma_%')),
group_by=[tt.c.thing_id],
order_by=sa.desc(
sa.func.sum(karma_sr_weight_cases(account_data))),
limit=NUM_TOP_USERS)
rows = s.execute().fetchall()
return [r.thing_id for r in rows]
def subreddits_with_custom_karma_multiplier():
type = tdb.types_id[Subreddit._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
karma = dt.alias(aliases.next())
q = sa.select(
[tt.c.thing_id],
sa.and_(tt.c.spam == False,
tt.c.deleted == False,
karma.c.thing_id == tt.c.thing_id,
karma.c.key == 'post_karma_multiplier'),
group_by = [tt.c.thing_id],
)
sr_ids = [r.thing_id for r in q.execute().fetchall()]
return Subreddit._byID(sr_ids, True, return_dict = False)
def top_users():
type = tdb.types_id[Account._type_id]
tt, dt = type.thing_table, type.data_table[0]
aliases = tdb.alias_generator()
account_data = dt.alias(aliases.next())
s = sa.select(
[tt.c.thing_id],
sa.and_(tt.c.spam == False,
tt.c.deleted == False,
account_data.c.thing_id == tt.c.thing_id,
account_data.c.key.like('karma_%')),
group_by = [tt.c.thing_id],
order_by = sa.desc(sa.func.sum(karma_sr_weight_cases(account_data))),
limit = NUM_TOP_USERS)
rows = s.execute().fetchall()
return [r.thing_id for r in rows]
def user_vote_change_links(period=None, user=None):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, link_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
link_dt = tdb.types_id[Link._type_id].data_table[0].alias(aliases.next())
# Create an SQL CASE statement for the subreddit vote multiplier
cases = []
for subreddit in subreddits_with_custom_karma_multiplier():
cases.append( (sa.cast(link_dt.c.value,sa.Integer) == subreddit._id,
subreddit.post_karma_multiplier) )
cases.append( (True, g.post_karma_multiplier) ) # The default article multiplier
weight_cases = sa.case(cases)
amount = sa.cast(rt.c.name, sa.Integer)
cols = [
author_dt.c.value,
sa.func.sum(sa.case([(amount > 0, amount * weight_cases)], else_=0)),
sa.func.sum(sa.case([(amount < 0, amount * -1 * weight_cases)], else_=0)),
]
query = sa.and_(author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
link_tt.c.thing_id == rt.c.thing2_id,
link_dt.c.key == 'sr_id',
link_dt.c.thing_id == rt.c.thing2_id)
if period is not None:
# Note: limiting this to only recent items is intentional
# http://lesswrong.com/r/discussion/lw/ghy/minor_separate_upvotes_and_downvotes_implimented/8d7f
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.extend((rt.c.date >= earliest, link_tt.c.date >= earliest))
if user is not None:
query.clauses.append(author_dt.c.value == str(user._id))
s = sa.select(cols, query, group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r[0]), (r[1], r[2])) for r in rows]
def user_vote_change_comments(period = '1 day'):
rel = Vote.rel(Account, Comment)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, comment_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
date = utils.timeago(period)
s = sa.select([author_dt.c.value, sa.func.sum(sa.cast(rt.c.name, sa.Integer))],
sa.and_(rt.c.date > date,
author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id'),
group_by = author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def top_user_change(period="1 day"):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account, link, dt = type.rel_table
aliases = tdb.alias_generator()
author = dt.alias(aliases.next())
date = utils.timeago(period)
s = sa.select(
[author.c.value, sa.func.sum(sa.cast(rt.c.name, sa.Integer))],
sa.and_(rt.c.date > date, author.c.thing_id == rt.c.rel_id, author.c.key == "author_id"),
group_by=author.c.value,
order_by=sa.desc(sa.func.sum(sa.cast(rt.c.name, sa.Integer))),
limit=10,
)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def user_vote_change_links(period='1 day'):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, link_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
link_dt = tdb.types_id[Link._type_id].data_table[0].alias(aliases.next())
# Create an SQL CASE statement for the subreddit vote multiplier
cases = []
for subreddit in subreddits_with_custom_karma_multiplier():
cases.append((sa.cast(link_dt.c.value, sa.Integer) == subreddit._id,
subreddit.post_karma_multiplier))
cases.append(
(True, g.post_karma_multiplier)) # The default article multiplier
date = utils.timeago(period)
s = sa.select([
author_dt.c.value,
sa.func.sum(sa.cast(rt.c.name, sa.Integer) * sa.case(cases))
],
sa.and_(rt.c.date >= date,
author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
link_tt.c.thing_id == rt.c.thing2_id,
link_tt.c.date >= date, link_dt.c.key == 'sr_id',
link_dt.c.thing_id == rt.c.thing2_id),
group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def top_user_change(period='1 day'):
rel = Vote.rel(Account, Link)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account, link, dt = type.rel_table
aliases = tdb.alias_generator()
author = dt.alias(aliases.next())
date = utils.timeago(period)
s = sa.select(
[author.c.value,
sa.func.sum(sa.cast(rt.c.name, sa.Integer))],
sa.and_(rt.c.date > date, author.c.thing_id == rt.c.rel_id,
author.c.key == 'author_id'),
group_by=author.c.value,
order_by=sa.desc(sa.func.sum(sa.cast(rt.c.name, sa.Integer))),
limit=10)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def user_vote_change_comments(period='1 day'):
rel = Vote.rel(Account, Comment)
type = tdb.rel_types_id[rel._type_id]
# rt = rel table
# dt = data table
rt, account_tt, comment_tt, dt = type.rel_table
aliases = tdb.alias_generator()
author_dt = dt.alias(aliases.next())
date = utils.timeago(period)
s = sa.select(
[author_dt.c.value,
sa.func.sum(sa.cast(rt.c.name, sa.Integer))],
sa.and_(rt.c.date >= date, author_dt.c.thing_id == rt.c.rel_id,
author_dt.c.key == 'author_id',
comment_tt.c.thing_id == rt.c.thing2_id,
comment_tt.c.date >= date),
group_by=author_dt.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
def user_karma_adjustments(period=None, user=None):
acct_info = tdb.types_id[Account._type_id]
acct_thing, acct_data = acct_info.thing_table, acct_info.data_table[0]
adj_info = tdb.types_id[KarmaAdjustment._type_id]
adj_thing, adj_data = adj_info.thing_table, adj_info.data_table[0]
aliases = tdb.alias_generator()
adj_data_2 = adj_data.alias(aliases.next())
query = sa.and_(adj_data.c.thing_id == adj_thing.c.thing_id,
adj_data.c.key == 'account_id',
adj_data.c.thing_id == adj_data_2.c.thing_id,
adj_data_2.c.key == 'amount')
if period is not None:
earliest = datetime.now(g.tz) - timedelta(0, period)
query.clauses.append(adj_thing.c.date >= earliest)
if user is not None:
query.clauses.append(adj_data.c.value == str(user._id))
s = sa.select([adj_data.c.value, sa.func.sum(sa.cast(adj_data_2.c.value, sa.Integer))],
query, group_by=adj_data.c.value)
rows = s.execute().fetchall()
return [(int(r.value), r.sum) for r in rows]
