Drizzle Team•2y ago

Query a table based on related table

How do I go about querying a table of let’s say visits that each have a date and have a relation to a table called jobs where one job could have many visits, how would I go about getting visits based on a date range that only belong to a job with a given “company id” I know how to get them in the date range but I’m not sure how to go about having a date range and() related jobs company Id without also storing the company id in each visit

1 Reply

FyzzOP•2y ago

// Jobs
export const jobs = mysqlTable("jobs", {
  id: varchar("id", { length: 50 }).primaryKey(),
  title: varchar("title", { length: 100 }).notNull(),
  instructions: varchar("instructions", { length: 400 }),
  company_id: varchar("company_id", { length: 50 }).notNull(),
  type: mysqlEnum("type", ["one_off", "recurring", "as_needed"]),
  property_id: varchar("property_id", { length: 50 }).notNull(),
  client_id: varchar("client_id", { length: 50 }).notNull(),
  rrule: varchar("rrule", { length: 500 }).notNull(),
  start_date: datetime("start_date").notNull(),
  end_date: datetime("end_date").notNull(),
  line_items: json("line_items").$type<LineItem[]>().default([]),
  status: mysqlEnum("status", ["active", "complete"])
    .default("active")
    .notNull(),
})

export const jobsRelations = relations(jobs, ({ one, many }) => ({
  property: one(properties, {
    fields: [jobs.property_id],
    references: [properties.id],
  }),
  client: one(clients, {
    fields: [jobs.client_id],
    references: [clients.id],
  }),
  company: one(companies, {
    fields: [jobs.company_id],
    references: [companies.id],
  }),
  visits: many(visits),
  employees: many(crews),
}))

// Visits
export const visits = mysqlTable("visits", {
  id: serial("id").autoincrement().primaryKey(),
  job_id: varchar("job_id", { length: 50 }).notNull(),
  time_in: datetime("time_in"),
  time_out: datetime("time_out"),
  time_total: int("time_total").default(0).notNull(),
  date: datetime("date").notNull(),
  status: mysqlEnum("status", [
    "waiting",
    "active",
    "skipped",
    "complete",
    "invoiced",
  ])
    .default("waiting")
    .notNull(),
  line_items: json("line_items").$type<LineItem[]>().default([]),
  visit_notes: varchar("visit_notes", { length: 400 }),
  visit_price: decimal("visit_price", { precision: 10, scale: 2 }),
})

export const visitsRelations = relations(visits, ({ one }) => ({
  job: one(jobs, {
    fields: [visits.job_id],
    references: [jobs.id],
  })
}))

// Jobs
export const jobs = mysqlTable("jobs", {
  id: varchar("id", { length: 50 }).primaryKey(),
  title: varchar("title", { length: 100 }).notNull(),
  instructions: varchar("instructions", { length: 400 }),
  company_id: varchar("company_id", { length: 50 }).notNull(),
  type: mysqlEnum("type", ["one_off", "recurring", "as_needed"]),
  property_id: varchar("property_id", { length: 50 }).notNull(),
  client_id: varchar("client_id", { length: 50 }).notNull(),
  rrule: varchar("rrule", { length: 500 }).notNull(),
  start_date: datetime("start_date").notNull(),
  end_date: datetime("end_date").notNull(),
  line_items: json("line_items").$type<LineItem[]>().default([]),
  status: mysqlEnum("status", ["active", "complete"])
    .default("active")
    .notNull(),
})

export const jobsRelations = relations(jobs, ({ one, many }) => ({
  property: one(properties, {
    fields: [jobs.property_id],
    references: [properties.id],
  }),
  client: one(clients, {
    fields: [jobs.client_id],
    references: [clients.id],
  }),
  company: one(companies, {
    fields: [jobs.company_id],
    references: [companies.id],
  }),
  visits: many(visits),
  employees: many(crews),
}))

// Visits
export const visits = mysqlTable("visits", {
  id: serial("id").autoincrement().primaryKey(),
  job_id: varchar("job_id", { length: 50 }).notNull(),
  time_in: datetime("time_in"),
  time_out: datetime("time_out"),
  time_total: int("time_total").default(0).notNull(),
  date: datetime("date").notNull(),
  status: mysqlEnum("status", [
    "waiting",
    "active",
    "skipped",
    "complete",
    "invoiced",
  ])
    .default("waiting")
    .notNull(),
  line_items: json("line_items").$type<LineItem[]>().default([]),
  visit_notes: varchar("visit_notes", { length: 400 }),
  visit_price: decimal("visit_price", { precision: 10, scale: 2 }),
})

export const visitsRelations = relations(visits, ({ one }) => ({
  job: one(jobs, {
    fields: [visits.job_id],
    references: [jobs.id],
  })
}))

 const visitList = await db.query.jobs
    .findMany({
      where: eq(jobs.company_id, user.privateMetadata.company_id as string),
      columns: {},
      with: {
        visits: {
          where: sql`DATE(${
            visits.date
          }) between ${startOfCalendar.toDate()} and ${endOfCalendar.toDate()}`,
        },
      },
    })
    .then((jobs) => jobs.flatMap((job) => job.visits))

 const visitList = await db.query.jobs
    .findMany({
      where: eq(jobs.company_id, user.privateMetadata.company_id as string),
      columns: {},
      with: {
        visits: {
          where: sql`DATE(${
            visits.date
          }) between ${startOfCalendar.toDate()} and ${endOfCalendar.toDate()}`,
        },
      },
    })
    .then((jobs) => jobs.flatMap((job) => job.visits))

This is what im looking for I belive 😅 maybe someone could tell me if this is correct

Gaming

Programming

Query a table based on related table