DT
Drizzle Team•3w ago
Daku

Aggregations in a query

const query = await db.query.folders.findMany({
            where: queryParams,
            columns: {
                ...folderSelector,
                hasFolders: sql<boolean>`EXISTS (SELECT 1 FROM ${folders} WHERE ${folders.parentId} = ${folders.id})`.as('hasFolders'),
            },
            with: {
                creator: userProfileSelector,
                organization: organizationUserSelector,
            },
        });
const query = await db.query.folders.findMany({
            where: queryParams,
            columns: {
                ...folderSelector,
                hasFolders: sql<boolean>`EXISTS (SELECT 1 FROM ${folders} WHERE ${folders.parentId} = ${folders.id})`.as('hasFolders'),
            },
            with: {
                creator: userProfileSelector,
                organization: organizationUserSelector,
            },
        });
Hello, is there a way to do aggregation within a query.
2 Replies
TOSL
TOSL•3w ago
Can you just move the subquery to a variable outside of the main query make sure you get back something valid and then pass whatever you need to the query?
Mario564
Mario564•2w ago
You can't do aggregations within columns, use extras for that purpose: 
{
  columns: {
    // select your columns, don't use `sql` here
  },
  extras: {
    // Use `sql` operator here
    hasFolders: sql<boolean>`...`.as('has_folders')
  }
}
{
  columns: {
    // select your columns, don't use `sql` here
  },
  extras: {
    // Use `sql` operator here
    hasFolders: sql<boolean>`...`.as('has_folders')
  }
}
@Daku

Did you find this page helpful?