Przejdź do głównej zawartości

Database Views

Using database views for complex reporting queries.

Overview​

Database views simplify complex queries by predefining joins and aggregations:

CREATE VIEW employee_time_summary AS
SELECT
  e.id AS employee_id,
  u."firstName",
  u."lastName",
  o.name AS organization_name,
  SUM(tl.duration) AS total_duration,
  COUNT(DISTINCT tl.id) AS log_count,
  MAX(tl."stoppedAt") AS last_tracked
FROM employee e
JOIN "user" u ON e."userId" = u.id
JOIN organization o ON e."organizationId" = o.id
LEFT JOIN time_log tl ON tl."employeeId" = e.id
GROUP BY e.id, u."firstName", u."lastName", o.name;

Common Views​

View NamePurpose
employee_time_summaryTime totals per employee
project_budget_statusBudget vs actual per project
monthly_invoice_totalsRevenue per month
task_completion_ratesTask status distribution

Materialized Views​

For computationally expensive queries:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  date_trunc('month', i."invoiceDate") AS month,
  i."organizationId",
  SUM(i."totalValue") AS total_revenue,
  COUNT(*) AS invoice_count
FROM invoice i
WHERE i.status = 'PAID'
GROUP BY 1, 2;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Usage in TypeORM​

@ViewEntity({
  expression: `SELECT ...`, // or reference a DB view
})
export class EmployeeTimeSummary {
  @ViewColumn()
  employeeId: string;

  @ViewColumn()
  totalDuration: number;
}