Question on composing queries together

[lithdew]

Suppose I have the following base query:

const accounts = 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);

I am attempting to represent the following query:

select * from (${accounts}) where num_posts_liked > 0

My attempt at building this query is as so, though is incorrect:

db.accounts.from(accounts).selectAll().where({ num_posts_liked: { gt: 0 } });

[romeerez]

Not supported yet, but I'm taking this to the development plan.

[romeerez]

This feature is ready now, update the packages to check it out.

Added method $from to ORM, so no need, in this case, to write db.accounts on the outer query.

With the tables defined in the related issues, this works:

const accounts = db.accounts
  .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 db.$from(accounts).where({ num_posts_liked: { gt: 0 } })
)

await db.$close()

[lithdew]

Is there any way to provide an alias to $from as well? I notice that $from by default aliases the subquery by the table it references from.

Also, does {left,right,inner,outer}[j|J]oin() support subqueries as well?

[romeerez]

Yes, there is a separate method .as to set alias:

await db.$from(accounts).as('t').where(...)

Here it's aliased as t.

Also, does {left,right,inner,outer}[j|J]oin() support subqueries as well?

Do you mean to get something like this?

SELECT * FROM table
JOIN ( SELECT * FROM other ) t ON true

No, currently not.

[lithdew]

Got it 👍 should I make a separate issue for having subqueries supported for joins?

[romeerez]

Yeah, make it please

[lithdew]

Seem to run into an error about type instantiation being too deep here:

error TS2589: Type instantiation is excessively deep and possibly infinite.

    12     const results2 = await db.$from(results).as("accounts_view").selectAll();
                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

const results = db.accounts.select("id", "publicKey", "name", "alias", "bio", "createdAt", "updatedAt", "lastSeenAt", "isVerified")
  .select({
    numPosts: q => db.posts.count().whereNotExists(db.commentsOnPosts, { commentId: "id" }).where(q.raw("posts.author_id = accounts.id")),
    numComments: q => db.posts.count().whereExists(db.commentsOnPosts, { commentId: "id" }).where(q.raw("posts.author_id = accounts.id")),
  });

const results2 = await db.$from(results).as("accounts_view").selectAll();

console.log(results2);