Reddit communities and votes with Alopex DB

What the service does

Reddit is home to many communities, from breaking news and sports to TV fan theories and cute animals. Posts and comments can be upvoted and downvoted, and votes decide what is surfaced in communities. The votes a user receives are also reflected in their karma. This page rebuilds communities, posts and votes.

Where the data lives

Alopex DB SQL tables: communities, users, posts, comments and votes live in SQL tables.
SQL: vote tallies, ranking and karma come from SQL aggregation.
Full-text and vector search: post text is found with full-text search, and similar posts with vector search over post embeddings.

Creating embedding vectors for posts is the job of the service code.

Deployment

On the service side, Alopex DB runs as a cluster. Posts and votes from many users are absorbed by adding nodes.

Why Alopex DB fits

Community data is the relationship between posts, comments and votes, and their tallies. Alopex DB keeps those relationships in SQL and runs full-text and vector search in the same engine.

See it in code

Communities, posts and votes live in tables; votes become a score through SQL aggregation. Just the ranking core.

import alopex

# サービス側で Alopex DB を動かす想定
db = alopex.Database.open("reddit.alopex")

# コミュニティ・投稿・投票を SQL テーブルで持つ
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY,
        community TEXT,
        author TEXT,
        title TEXT
    )
""")
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS votes (
        post_id INTEGER,
        voter TEXT,
        value INTEGER
    )
""")

# 投稿と投票を書き込む
db.execute_sql("INSERT INTO posts (id, community, author, title) VALUES (?, ?, ?, ?)", [1, "cats", "alice", "うちの猫"])
db.execute_sql("INSERT INTO posts (id, community, author, title) VALUES (?, ?, ?, ?)", [2, "cats", "bob", "猫の爪とぎ"])
for post_id, voter, value in [(1, "bob", 1), (1, "carol", 1), (2, "alice", 1), (2, "carol", -1)]:
    db.execute_sql("INSERT INTO votes (post_id, voter, value) VALUES (?, ?, ?)", [post_id, voter, value])

# 投票を集計し、スコアの高い順に並べる（コミュニティの表示順）
rows = db.execute_sql("""
    SELECT p.title, SUM(v.value) AS score
    FROM posts AS p
    JOIN votes AS v ON v.post_id = p.id
    WHERE p.community = ?
    GROUP BY p.id, p.title
    ORDER BY score DESC
""", ["cats"])

for row in rows:
    print(row["score"], row["title"])

Summing the votes gives the score, and ordering by it is the display order itself.

Build a community with Alopex DB, which handles vote tallies and similar-post search in the same engine.