Example #1
0
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]
Example #2
0
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]
Example #3
0
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]
Example #4
0
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]
Example #5
0
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]
Example #6
0
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]
Example #7
0
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]
Example #8
0
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]
Example #9
0
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]
Example #10
0
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]
Example #11
0
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)
Example #12
0
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]
Example #13
0
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)
Example #14
0
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]
Example #15
0
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]
Example #16
0
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]
Example #17
0
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]
Example #18
0
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]
Example #19
0
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]
Example #20
0
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]
Example #21
0
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]