GT Posts - Answer Overflow

•Created by GT on 2/27/2025 in #help

Time Mysql Formatting

The Time format come formatted like hh:mm:ss.mmmmmm ( unsure if the last part is ms. Just like in the following query result:

{
    id: 2,
    employeeId: 1,
    day: 1,
    startTime: '09:02:00.000000',
    endTime: '22:00:00.000000'
  }

{
    id: 2,
    employeeId: 1,
    day: 1,
    startTime: '09:02:00.000000',
    endTime: '22:00:00.000000'
  }

In the Drizzle Studio, it shows as hh:mm - which is what I want - If that's not something I did wrong, what should I use to get it in that format?

4 replies

DTDrizzle Team

•Created by GT on 2/18/2025 in #help

Drizzle Runner Error

4 replies

DTDrizzle Team

•Created by GT on 2/18/2025 in #help

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?

5 replies

DTDrizzle Team

•Created by GT on 2/10/2025 in #help

Time type query inconsistencies

I got two different queires:

const result = await database.query.employeesTable.findFirst({
        where: eq(employeesTable.id, employeeId),
        with: {
            availabilities: true
        }
    });

const result = await database.query.employeesTable.findFirst({
        where: eq(employeesTable.id, employeeId),
        with: {
            availabilities: true
        }
    });

    const response = await database.query.eventTable.findFirst({
        where: eq(eventTable.id, eventId),
        with: {
            tags: true,
            allocations: {
                with: {
                    employee: {
                        with: {
                            availabilities: {
                                where: and(
                                    gte(availabilityTable.endTime, getOuterEventField(eventTable.endTime)),
                                    lte(availabilityTable.startTime, getOuterEventField(eventTable.startTime)),
                                    eq(availabilityTable.day, sql`WEEKDAY(${getOuterEventField(eventTable.date)})`)
                                )
                            }
                        }
                    }
                }
            }
        }
    }).execute()

    const response = await database.query.eventTable.findFirst({
        where: eq(eventTable.id, eventId),
        with: {
            tags: true,
            allocations: {
                with: {
                    employee: {
                        with: {
                            availabilities: {
                                where: and(
                                    gte(availabilityTable.endTime, getOuterEventField(eventTable.endTime)),
                                    lte(availabilityTable.startTime, getOuterEventField(eventTable.startTime)),
                                    eq(availabilityTable.day, sql`WEEKDAY(${getOuterEventField(eventTable.date)})`)
                                )
                            }
                        }
                    }
                }
            }
        }
    }).execute()

The first outputs the Time field as:

  availabilities: [
    {
      id: 2,
      day: 0,
      startTime: "22:02:00.000000",
      endTime: "22:02:00.000000",
      status: "UNCHANGED"
    }
  ]

  availabilities: [
    {
      id: 2,
      day: 0,
      startTime: "22:02:00.000000",
      endTime: "22:02:00.000000",
      status: "UNCHANGED"
    }
  ]

The latter, outputs it as:

{
  startTime: "22:29:00",
  endTime: "23:31:00",
  ...
}

{
  startTime: "22:29:00",
  endTime: "23:31:00",
  ...
}

Is the nesting that causes the conversion? How could I have both returning in the hh/mm/ss format?

2 replies

DTDrizzle Team

•Created by GT on 12/27/2024 in #help

Exclude results with empty relation

I tried using isNotNull to filter out the results that doesn't has any value in availabilities relation, but didn't worked that way, I'm sure I'm missing an easier way of doing it.

13 replies

Gaming

Programming