Advanced Topics
Subqueries & Aggregates
Using subqueries and aggregate functions
This guide covers advanced query patterns using subqueries and aggregate functions.
Subqueries
Subqueries allow you to use the result of one query within another query. They're powerful for complex filtering and comparisons.
Creating Subqueries
import { subquery, scalarSubquery } from '@type32/tauri-sqlite-orm';
import { eq, inArray, exists } from '@type32/tauri-sqlite-orm';
const users = sqliteTable('users', {
id: integer('id').primaryKey().autoincrement(),
name: text('name').notNull(),
departmentId: integer('department_id').notNull(),
});
const departments = sqliteTable('departments', {
id: integer('id').primaryKey().autoincrement(),
name: text('name').notNull(),
});
Subquery in IN Clause
// Find all users in the 'Engineering' department
const engineeringQuery = db.select(departments)
.where(eq(departments._.columns.name, 'Engineering'));
const engineeringUsers = await db.select(users)
.where(inArray(users._.columns.departmentId, subquery(engineeringQuery)))
.all();
// Generated SQL (simplified):
// SELECT * FROM users
// WHERE department_id IN (SELECT id FROM departments WHERE name = 'Engineering')
Subquery with NOT IN
import { notIn } from '@type32/tauri-sqlite-orm';
// Find users NOT in the 'HR' department
const hrQuery = db.select(departments)
.where(eq(departments._.columns.name, 'HR'));
const nonHrUsers = await db.select(users)
.where(notIn(users._.columns.departmentId, subquery(hrQuery)))
.all();
EXISTS Subqueries
const posts = sqliteTable('posts', {
id: integer('id').primaryKey().autoincrement(),
title: text('title').notNull(),
authorId: integer('author_id').notNull(),
});
// Find users who have written at least one post
const usersWithPosts = await db.select(users)
.where(
exists(subquery(
db.select(posts)
.where(eq(posts._.columns.authorId, users._.columns.id))
))
)
.all();
// Find users who haven't written any posts
const usersWithoutPosts = await db.select(users)
.where(
notExists(subquery(
db.select(posts)
.where(eq(posts._.columns.authorId, users._.columns.id))
))
)
.all();
Scalar Subquery Comparisons
import { gtSubquery, ltSubquery, eqSubquery } from '@type32/tauri-sqlite-orm';
const orders = sqliteTable('orders', {
id: integer('id').primaryKey().autoincrement(),
userId: integer('user_id').notNull(),
total: integer('total').notNull(),
});
// Find orders greater than the average
const avgQuery = scalarSubquery(
db.db.select('SELECT AVG(total) FROM orders')
);
const aboveAverageOrders = await db.select(orders)
.where(gtSubquery(orders._.columns.total, avgQuery))
.all();
Aggregate Functions
Aggregate functions perform calculations on sets of rows. The ORM provides type-safe aggregate functions.
Available Aggregates
import {
count,
countDistinct,
sum,
avg,
min,
max,
groupConcat
} from '@type32/tauri-sqlite-orm';
Using Aggregates
While the query builder doesn't yet have a dedicated aggregate API, you can use aggregates with raw SQL:
// Count all users
const result = await db.db.select('SELECT COUNT(*) as count FROM users');
console.log(result[0].count);
// Or use the .count() helper
const count = await db.select(users).count();
GROUP BY with Aggregates
const posts = sqliteTable('posts', {
id: integer('id').primaryKey().autoincrement(),
title: text('title').notNull(),
authorId: integer('author_id').notNull(),
views: integer('views').notNull().default(0),
});
// Count posts per author
const postsByAuthor = await db.db.select(`
SELECT
author_id,
COUNT(*) as post_count,
SUM(views) as total_views,
AVG(views) as avg_views
FROM posts
GROUP BY author_id
`);
SQLite GROUP_CONCAT
// Concatenate related values
const tagsByPost = await db.db.select(`
SELECT
post_id,
GROUP_CONCAT(tag_name, ', ') as tags
FROM post_tags
GROUP BY post_id
`);
// Result: [{ post_id: 1, tags: 'javascript, typescript, node' }, ...]
Complex Example: Subquery + Aggregates
const products = sqliteTable('products', {
id: integer('id').primaryKey().autoincrement(),
name: text('name').notNull(),
categoryId: integer('category_id').notNull(),
price: integer('price').notNull(),
});
const categories = sqliteTable('categories', {
id: integer('id').primaryKey().autoincrement(),
name: text('name').notNull(),
});
// Find categories with above-average priced products
const avgPriceQuery = db.db.select('SELECT AVG(price) FROM products');
const expensiveCategories = await db.select(categories)
.where(
exists(subquery(
db.select(products)
.where(eq(products._.columns.categoryId, categories._.columns.id))
.where(gtSubquery(
products._.columns.price,
scalarSubquery(avgPriceQuery)
))
))
)
.all();
Best Practices
- Use
.count()helper instead of raw COUNT(*) when possible - Index subquery columns for better performance
- Test subquery performance - sometimes JOINs are faster
- Use
.toSQL()to verify what SQL is generated - Consider using relations instead of manual subqueries when possible
Performance Tips
// ❌ Slow: Correlated subquery for each row
const usersWithPostCount = await db.db.select(`
SELECT u.*,
(SELECT COUNT(*) FROM posts WHERE author_id = u.id) as post_count
FROM users u
`);
// ✅ Better: Use a JOIN or relation with .count()
const usersWithPosts = await db.select(users)
.include({ posts: true })
.all();
// Then count in JavaScript:
const usersWithCounts = usersWithPosts.map(user => ({
...user,
postCount: user.posts.length
}));
Debugging Subqueries
// Build subquery first
const subq = db.select(departments)
.where(eq(departments._.columns.name, 'Engineering'));
console.log('Subquery:', subq.toSQL());
// Then use in main query
const mainQuery = db.select(users)
.where(inArray(users._.columns.departmentId, subquery(subq)));
console.log('Main query:', mainQuery.toSQL());
// Execute
const results = await mainQuery.all();
Subqueries and aggregates give you powerful tools for complex data analysis while maintaining type safety!