[FEATURE]: Subquery select column about drizzle-orm HOT 3 OPEN

@MatanYadaev I believe you can already do that but you also have to join the sub-query to the main one and maybe have some GROUP BY clause.

Have you tried doing something like this?

const subquery = db
  .select({
    userId: posts.userId,
    postsCount: count().as('postsCount'),
  })
  .from(posts)
  .groupBy(posts.userId)
  .as('postsCount');

const users = await db
  .select({
    id: users.id,
    postsCount: subquery.postsCount,
  })
  .from(users)
  .innerJoin(subquery, eq(subquery.userId, posts.userId));

Does that work for you?

from drizzle-orm.

It might work, but I didn't want to use inner join in my use case. This is my expected query:

SELECT 
    users.id,
    (
        SELECT 
            COUNT(*) 
        FROM 
            posts
        WHERE 
            posts.userId = users.id
    ) AS postsCount
FROM 
    users;

from drizzle-orm.

Well, in that case I don't think it will be added any time soon, especially because of the presence of the alternative I suggested...

Also keep in mind that JOINs are generally faster than subqueries if you have a large dataset, so the existing solution would also be better than the feature you requested.

In this specific use-case where you put the subquery in the SELECT statement, it will be executed for every single row of users and things can get slow really fast

from drizzle-orm.