import { createBaseTable, orchidORM } from "orchid-orm";
export const BaseTable = createBaseTable({
columnTypes: t => ({
...t,
text: (min = 0, max = 1024) => t.text(min, max),
})
});
export class AccountsTable extends BaseTable {
table = "accounts";
columns = this.setColumns(t => ({
id: t.text().primaryKey(),
public_key: t.text(),
name: t.text(),
alias: t.text().nullable(),
bio: t.text(),
avatar_storage_id: t.text().nullable(),
avatar_etag: t.text().nullable(),
created_at: t.timestamp(),
updated_at: t.timestamp(),
last_seen_at: t.timestamp(),
is_verified: t.boolean(),
}));
relations = {
cover: this.hasOne(() => AccountCoversTable, {
primaryKey: "id",
foreignKey: "account_id",
}),
oauths: this.hasMany(() => AccountOauthsTable, {
primaryKey: "id",
foreignKey: "account_id",
}),
account_posts: this.hasMany(() => PostsTable, {
primaryKey: "id",
foreignKey: "author_id",
}),
account_likes: this.hasMany(() => LikesOnPostsTable, {
primaryKey: "id",
foreignKey: "liker_id",
}),
};
}
export class AccountCoversTable extends BaseTable {
table = "account_covers";
columns = this.setColumns(t => ({
account_id: t.text().foreignKey("accounts", "id").primaryKey(),
storage_id: t.text(),
etag_id: t.text(),
created_at: t.timestamp(),
updated_at: t.timestamp(),
}));
}
export class AccountOauthsTable extends BaseTable {
table = "account_oauths";
columns = this.setColumns(t => ({
account_id: t.text().foreignKey("accounts", "id").primaryKey(),
provider_id: t.enum("oauth_provider_type", ["instagram", "twitter"]),
user_id: t.text(),
username: t.text(),
}));
}
export class PostsTable extends BaseTable {
table = "posts";
columns = this.setColumns(t => ({
id: t.text().primaryKey(),
author_id: t.text().foreignKey("accounts", "id"),
content: t.text(),
created_at: t.timestamp(),
updated_at: t.timestamp(),
}));
relations = {
comments: this.hasMany(() => CommentsOnPostsTable, {
primaryKey: "id",
foreignKey: "post_id",
}),
likes: this.hasMany(() => LikesOnPostsTable, {
primaryKey: "id",
foreignKey: "post_id",
}),
};
}
export class CommentsOnPostsTable extends BaseTable {
table = "comments_on_posts";
columns = this.setColumns(t => ({
post_id: t.text().foreignKey("posts", "id").primaryKey(),
comment_id: t.text().foreignKey("comments", "id"),
}));
}
export class LikesOnPostsTable extends BaseTable {
table = "likes_on_posts";
columns = this.setColumns(t => ({
liker_id: t.text().foreignKey("accounts", "id").primaryKey(),
post_id: t.text().foreignKey("posts", "id").primaryKey(),
created_at: t.timestamp(),
}));
}
export const db = orchidORM({ databaseURL: process.env["DATABASE_URL"], autoPreparedStatements: true, log: true }, {
accounts: AccountsTable,
accountCovers: AccountCoversTable,
accountOauths: AccountOauthsTable,
posts: PostsTable,
commentsOnPosts: CommentsOnPostsTable,
LikesOnPosts: LikesOnPostsTable,
});
const results = db.accounts
.leftJoin("cover")
.selectAll()
.select({
cover: q => q.cover.selectAll(),
num_posts: q => q.account_posts.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "id"),
num_comments: q => q.account_posts.count().whereExists(db.commentsOnPosts, "comment_id", "=", "id"),
num_posts_liked: q => q.account_likes.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
num_comments_liked: q => q.account_likes.count().whereExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
})
.limit(10);
console.log(await results);
% node --no-warnings --loader ts-node/esm src/db.orchid.ts
(39.7ms) SELECT "accounts".*, (SELECT row_to_json("t".*) FROM (SELECT * FROM "account_covers" AS "cover" WHERE "cover"."account_id" = "accounts"."id" LIMIT $1) AS "t") AS "cover", (SELECT count(*) FROM "posts" AS "account_posts" WHERE NOT EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_posts"."id" LIMIT 1) AND "account_posts"."author_id" = "accounts"."id") AS "num_posts", (SELECT count(*) FROM "posts" AS "account_posts" WHERE EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_posts"."id" LIMIT 1) AND "account_posts"."author_id" = "accounts"."id") AS "num_comments", (SELECT count(*) FROM "likes_on_posts" AS "account_likes" WHERE NOT EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_likes"."post_id" LIMIT 1) AND "account_likes"."liker_id" = "accounts"."id") AS "num_posts_liked", (SELECT count(*) FROM "likes_on_posts" AS "account_likes" WHERE EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_likes"."post_id" LIMIT 1) AND "account_likes"."liker_id" = "accounts"."id") AS "num_comments_liked" FROM "accounts" LEFT JOIN "account_covers" AS "cover" ON "cover"."account_id" = "accounts"."id" LIMIT $2 [1, 10]
file:///Users/dew/node_modules/pqb/src/queryMethods/then.ts:41
temporaryError = new Error();
^
Error
at Db.get then [as then] (file:///Users/dew/node_modules/pqb/src/queryMethods/then.ts:41:22)
at file:///Users/dew/src/db.orchid.ts:139:19
at ModuleJob.run (node:internal/modules/esm/module_job:193:25)
at async Promise.all (index 0)
at async ESMLoader.import (node:internal/modules/esm/loader:530:24)
at async loadESM (node:internal/process/esm_loader:91:5)
at async handleMainPromise (node:internal/modules/run_main:65:12)
The query alone however is built correctly and works should I manually execute it in my PostgreSQL database.