[FEATURE]: Subquery select column

[MatanYadaev]

Describe what you want

I would love to have support for subquery select column. Currently it's only supported to use subqueries in the .from() method and not in the .select() method. It seems the same but there are differences.

Expected behavior

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

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

Current behavior

const users = await db
  .select({
    id: users.id,
    postsCount:
      sql<number>`(SELECT COUNT(*) FROM ${posts} WHERE ${posts}.${posts.userId} = ${users}.${users.id})`.as(
        'postsCount',
      ),
  })
  .from(users);

[TheGrinch00]

@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?

[MatanYadaev]

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;

[TheGrinch00]

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

[jeesus]

Bump! I don't see why this kind of easy feature was never implemented.

@TheGrinch00 like you said, it depends on the dataset. On certain scenarios JOIN with GROUP BY is far more slower than a subquery in SELECT.