Shad Posts - Answer Overflow

Shad

Explore posts from servers

•Created by Shad on 4/16/2024 in #help

Query `count` returning the wrong value

Hi everyone! I'm hoping someone can help me understand the count() function better. My goal is to retrieve the count of all records in the join. I have the following query:

const selectResult = await db
      .select({
        userId: users.id,
        organizationMembershipId: organizationMemberships.id,
        joinedOrganization: organizationMemberships.createdAt,
        firstName: users.firstName,
        lastName: users.lastName,
        emailAddress: users.emailAddress,
        phoneNumber: users.phoneNumber,
        imageUrl: users.imageUrl,
        lastSignIn: users.lastSignIn,
        role: organizationMemberships.role,
        count: count(),
      })
      .from(organizationMemberships)
      .innerJoin(users, eq(organizationMemberships.userId, users.id))
      .limit(pagination.pageSize)
      .offset(pagination.pageIndex * pagination.pageSize)
      .groupBy(sql`${users.id}, ${organizationMemberships.id}`);

const selectResult = await db
      .select({
        userId: users.id,
        organizationMembershipId: organizationMemberships.id,
        joinedOrganization: organizationMemberships.createdAt,
        firstName: users.firstName,
        lastName: users.lastName,
        emailAddress: users.emailAddress,
        phoneNumber: users.phoneNumber,
        imageUrl: users.imageUrl,
        lastSignIn: users.lastSignIn,
        role: organizationMemberships.role,
        count: count(),
      })
      .from(organizationMemberships)
      .innerJoin(users, eq(organizationMemberships.userId, users.id))
      .limit(pagination.pageSize)
      .offset(pagination.pageIndex * pagination.pageSize)
      .groupBy(sql`${users.id}, ${organizationMemberships.id}`);

This returns 2 records, which is correct, however, the count value is 1. I think something is wrong with my groupBy clause but not sure what. I was getting errors until I add that groupBy clause and the data returned is correct, just the count is wrong. The relationship between users and organizationMemberships is one-to-many. Please let me know if there are any other details I need to share

3 replies

Gaming

Programming