# Query Builder Patterns {#query-builder-patterns}


Use TypeORM query builder for complex queries.


## Basic Query Builder {#basic-query-builder}


```typescript
const employees = await this.employeeRepository
  .createQueryBuilder("employee")
  .leftJoinAndSelect("employee.user", "user")
  .where("employee.organizationId = :orgId", { orgId })
  .andWhere("employee.tenantId = :tenantId", { tenantId })
  .andWhere("employee.isActive = :isActive", { isActive: true })
  .orderBy("user.firstName", "ASC")
  .skip(skip)
  .take(take)
  .getManyAndCount();
```


## Filtering Patterns {#filtering-patterns}



### Dynamic Filters {#dynamic-filters}


```typescript
const qb = this.taskRepository.createQueryBuilder("task");

if (filters.status) {
  qb.andWhere("task.status = :status", { status: filters.status });
}
if (filters.projectId) {
  qb.andWhere("task.projectId = :projectId", { projectId: filters.projectId });
}
if (filters.search) {
  qb.andWhere("task.title ILIKE :search", { search: `%${filters.search}%` });
}
```


### Date Range {#date-range}


```typescript
qb.andWhere("timeLog.startedAt BETWEEN :start AND :end", {
  start: new Date("2025-01-01"),
  end: new Date("2025-12-31"),
});
```


### IN Clause {#in-clause}


```typescript
qb.andWhere("task.status IN (:...statuses)", {
  statuses: ["TODO", "IN_PROGRESS"],
});
```


## Aggregate Queries {#aggregate-queries}


```typescript
// Total hours per employee
const result = await this.timeLogRepository
  .createQueryBuilder("timeLog")
  .select("timeLog.employeeId", "employeeId")
  .addSelect("SUM(timeLog.duration)", "totalDuration")
  .where("timeLog.tenantId = :tenantId", { tenantId })
  .groupBy("timeLog.employeeId")
  .getRawMany();
```


## Subqueries {#subqueries}


```typescript
const subQuery = this.taskRepository
  .createQueryBuilder("t")
  .select("t.projectId")
  .where("t.status = :status", { status: "DONE" });

const projects = await this.projectRepository
  .createQueryBuilder("project")
  .where(`project.id IN (${subQuery.getQuery()})`)
  .setParameters(subQuery.getParameters())
  .getMany();
```


## Related Pages {#related-pages}


- [Database Schema](https://sourcey-gauzy.pages.dev/database/schema-overview/index.md) — schema overview
- [Database Indexing](https://sourcey-gauzy.pages.dev/database/indexing-strategy/index.md) — optimize queries
- [Repository Pattern](https://sourcey-gauzy.pages.dev/architecture/repository-pattern/index.md) — repository architecture