Creating a View with Turso/Sqlite

export const jobsView = sqliteView("jobsView").as((qb) =>
  qb
    .select({
      id: job.id,
      title: job.title,
      company_department: job.companyDepartment,
      url: job.url,
      job_location: job.location,
      description: job.description,
      company: company.name,
      company_slug: company.slug,
      company_domain: company.domain,
      company_type: companyMetadata.type,
      departments: normalized_department.normalized,
      locations: normalized_location.normalized,
      earliest_known: sql`(STRFTIME('%Y-%m-%d', job.earliest_known))`.as(
        "earliest_known_formatted",
      ),
      last_seen: sql`(MAX(joblog.seen_on))`.as("last_seen"),
      days: sql`
      CAST(ROUND(julianday(MAX(joblog.seen_on)) - julianday(DATE(job.earliest_known)) + 1) AS INTEGER)`
        .as("days"),
    })
    .from(job)
    .leftJoin(company, eq(job.companyId, company.id))
    .leftJoin(
      normalized_department,
      eq(job.normDepartmentId, normalized_department.id),
    )
    .leftJoin(
      normalized_location,
      eq(job.normLocationId, normalized_location.id),
    )
    .leftJoin(joblog, eq(job.id, joblog.jobId))
    .leftJoin(companyMetadata, eq(company.domain, companyMetadata.domain))
    .groupBy(job.id)
);

export const jobsView = sqliteView("jobsView").as((qb) =>
  qb
    .select({
      id: job.id,
      title: job.title,
      company_department: job.companyDepartment,
      url: job.url,
      job_location: job.location,
      description: job.description,
      company: company.name,
      company_slug: company.slug,
      company_domain: company.domain,
      company_type: companyMetadata.type,
      departments: normalized_department.normalized,
      locations: normalized_location.normalized,
      earliest_known: sql`(STRFTIME('%Y-%m-%d', job.earliest_known))`.as(
        "earliest_known_formatted",
      ),
      last_seen: sql`(MAX(joblog.seen_on))`.as("last_seen"),
      days: sql`
      CAST(ROUND(julianday(MAX(joblog.seen_on)) - julianday(DATE(job.earliest_known)) + 1) AS INTEGER)`
        .as("days"),
    })
    .from(job)
    .leftJoin(company, eq(job.companyId, company.id))
    .leftJoin(
      normalized_department,
      eq(job.normDepartmentId, normalized_department.id),
    )
    .leftJoin(
      normalized_location,
      eq(job.normLocationId, normalized_location.id),
    )
    .leftJoin(joblog, eq(job.id, joblog.jobId))
    .leftJoin(companyMetadata, eq(company.domain, companyMetadata.domain))
    .groupBy(job.id)
);