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
// 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))