Drizzle Team•7mo ago

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

2 Replies

san4d•7mo ago

Your group by should look like

groupBy(users.id, organizationMemberships.id)

groupBy(users.id, organizationMemberships.id)

I'm not sure about the count.

Sillvva•7mo ago

That's not how count in SQL works in a GROUP BY query. It counts the number of records that match the group by criteria. For example, if you had a table with a category_id, and you grouped it by that column, the value of count in the resulting dataset would be the number of records matching each category_id in the table. So:

category_id
1
1
2
2
2
3

category_id
1
1
2
2
2
3

Then SELECT COUNT(*) FROM table GROUP BY category_id

category_id  count
1            2
2            3
3            1

category_id  count
1            2
2            3
3            1

Instead, you can get the total number of records returned like this: selectResult.length

Gaming

Programming

Query `count` returning the wrong value