DT
Drizzle Team•3mo ago
GT

Filter Empty Relations out

I got both tables as defined in my schema file: 
export const employeesTable = table(
    'employees', {
    id: int().autoincrement().primaryKey(),
    fullName: varchar("full_name", { length: 255 }).notNull(),
    cellphone: varchar("cellphone", { length: 15 }).notNull(),
    cpf: varchar( { length: 14 }).notNull().unique(),
});

export const availabilityTable = table(
    'employees_availability', {
        id: int().autoincrement().primaryKey(),
        employeeId: int().notNull().references((): AnyMySqlColumn => employeesTable.id),
        day: int().notNull(),
        startTime: time().notNull(),
        endTime: time().notNull(),
    }
);

...
export const employeesTable = table(
    'employees', {
    id: int().autoincrement().primaryKey(),
    fullName: varchar("full_name", { length: 255 }).notNull(),
    cellphone: varchar("cellphone", { length: 15 }).notNull(),
    cpf: varchar( { length: 14 }).notNull().unique(),
});

export const availabilityTable = table(
    'employees_availability', {
        id: int().autoincrement().primaryKey(),
        employeeId: int().notNull().references((): AnyMySqlColumn => employeesTable.id),
        day: int().notNull(),
        startTime: time().notNull(),
        endTime: time().notNull(),
    }
);

...
How could I query all Employees that has availabilities? An simple query as this one: 
database.query.employeesTable.findMany({
  with: { availabilities: true}
});
database.query.employeesTable.findMany({
  with: { availabilities: true}
});
Would also return empty availabilities, filtering out the result after the query will result in inconsistencies in the result size. How could I resolve that?
2 Replies
TOSL
TOSL•3mo ago
Switch {availabilities: true} to a where that uses one of the following: https://orm.drizzle.team/docs/operators#exists https://orm.drizzle.team/docs/operators#isnotnull
Drizzle ORM - Filters
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
GT
GTOP•3mo ago
Actually both I think that wouldn't work in this case As Drizzle doesn't allow to access fields from relations I solved it this way: 
const employessWithAvailabilities = database.selectDistinct({ data: availabilityTable.employeeId })
                .from(availabilityTable).where(and(...availabilityConditions));
const employessWithAvailabilities = database.selectDistinct({ data: availabilityTable.employeeId })
                .from(availabilityTable).where(and(...availabilityConditions));
Then, in the where clause I added: inArray(EmployeesTable.id, employessWithAvailabilities) Unsure about the performance-wise or if there's better ways to write it, that was the only way I could make it work as I intended The whole function is right here

Did you find this page helpful?