Drizzle Team•12mo ago

Using `count` in CTE has type `never`

I got the following tables: users: id, ... followers: followingId, followerId I'm trying to write a with common table expression to reuse in other queries:

const withUser = db.$with('user').as(
    db
      .select({
        id: users.id,
        // ...
        following: countDistinct(following.followingId),
        followers: countDistinct(followers.followerId)
      })
      .from(users)
      .leftJoin(following, eq(following.followerId, users.id))
      .leftJoin(followers, eq(followers.followingId, users.id))

const withUser = db.$with('user').as(
    db
      .select({
        id: users.id,
        // ...
        following: countDistinct(following.followingId),
        followers: countDistinct(followers.followerId)
      })
      .from(users)
      .leftJoin(following, eq(following.followerId, users.id))
      .leftJoin(followers, eq(followers.followingId, users.id))

The problem is that now, if I try to use the results of that CTE, sq.following and sq.followers are not valid columns:

const result = db
  .with(withUser)
  .select({ 
    following: withUser.following //< sq.following has type never, result[0].following is never
   })
  .from(withUser);

const result = db
  .with(withUser)
  .select({ 
    following: withUser.following //< sq.following has type never, result[0].following is never
   })
  .from(withUser);

1 Reply

AstroBearOP•12mo ago

Fixed, I had to use countDistinct(following.followingId).as('following') etc.

Gaming

Programming

Using `count` in CTE has type `never`