Subqueries support in db.select()

[AlexBlokh]

Drizzle ORM allows you to build subqueries and pass them to db.select().from(subquery), yet drizzle is missing subqueries in list of select params which prevents you from doing very handy SQL statements like

select 
  users.*, 
  (select count(*) from posts where posts.author_id = users.id) as postsCount
from users;

To address this we wanna ship support for drizzle subqueries in list of select params which will let you do

db.select({
  ...getTableColumns(users),
  totalPostsCount: db.select({ count: count() }).from(posts).where(eq(posts.id, users.authorId))
}).from(users);

while you now have a handy db.$count() operator which does exactly that and yes, we're going to widen list of convenient aggregation $operators, this is meant to cover more of the SQL and unlock further improvements with the subsequent release of db.select(count()).from(users) and more advanced aggregations like

db.select({
  ...getTableColumns(users),
  posts: coalesce(db.select(jsonAgg(rowToJson())).from(posts).where(eq(posts.authorId, users.id)), jsonArr())
}).from(users);

// with the result of type
type res = {
  id: number,
  name: string, // and other users fields
  posts: {
    id: number,
    authorId: number,
    text: string,
  }[]
}[];

This API is already supported in our relational queries v2 in extras fields

UPD 1:
we don't need to enforce users to pass subquery with mandatory .as(), we are safe to provide key name as a as value to SQL query if .as is absent.

PS: this will finally close #361