Drizzle Team•16mo ago

one to one query

I have a 2 models relation one to one, visit and visit_log, visit_log stores the visitId for reference to the visit table

const result = await this.db.query.visit.findMany({
      where: and(eq(visit.userId, userId)),
      with: {
        visitLog: {}, // Want to filter by a property ???
      },
});

const result = await this.db.query.visit.findMany({
      where: and(eq(visit.userId, userId)),
      with: {
        visitLog: {}, // Want to filter by a property ???
      },
});

This is the result that I want, however I want to use a where inside visitLog to filter by status in other hands this is working but typescript is throwing error

const result2 = await this.db
      .select({
        visit: {
          ...visit,
          // @ts-ignore Working but errors
          visitLog,
        },
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

const result2 = await this.db
      .select({
        visit: {
          ...visit,
          // @ts-ignore Working but errors
          visitLog,
        },
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

The result I want

[{
    visit: {
      visitId: '6d15badd-19a1-4de7-977e-ad13e387173d',
      visitLog: [Object]
    }
  }],

[{
    visit: {
      visitId: '6d15badd-19a1-4de7-977e-ad13e387173d',
      visitLog: [Object]
    }
  }],

both are working but the first one i'm not able to filter on visitLog object. in the second aswell are working but there are typescript errors .

5 Replies

NinjaBunny•16mo ago

for the first issue, I believe they removed the ability for nested where statements in relational queries, but it still exists in older versions and for the second statement, can you past the entire query?

SiumauricioOP•16mo ago

This is working fine on the second statement only ts errors are the issue

SELECT "visit"."visitId",
       "visit"."userId",
       "visit_log"."visitLogId",
       "visit_log"."status",
       "visit_log"."visitId"
FROM "visit"
INNER JOIN "visit_log" ON "visit"."visitId" = "visit_log"."visitId"
WHERE ("visit_log"."status" = $1
       AND "visit"."userId" = $2) -- params: ["pending", "0440c3a3-81e9-43c8-a9a3-8bfd91ed5826"]

SELECT "visit"."visitId",
       "visit"."userId",
       "visit_log"."visitLogId",
       "visit_log"."status",
       "visit_log"."visitId"
FROM "visit"
INNER JOIN "visit_log" ON "visit"."visitId" = "visit_log"."visitId"
WHERE ("visit_log"."status" = $1
       AND "visit"."userId" = $2) -- params: ["pending", "0440c3a3-81e9-43c8-a9a3-8bfd91ed5826"]

Angelelz•16mo ago

The type you're passing to .select() is a table type, like an internal type for drizzle

const result2 = await this.db
      .select({
        visit: {
          ...visit,
          // @ts-ignore Working but errors
          visitLog: ...getTableColumns(visitLog),
        },
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

const result2 = await this.db
      .select({
        visit: {
          ...visit,
          // @ts-ignore Working but errors
          visitLog: ...getTableColumns(visitLog),
        },
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

This should get rid of the type error

SiumauricioOP•16mo ago

Thanks @angelelz! I modify and it works now

    const result = await this.db
      .select({
        ...getTableColumns(visit),
        visitLog: getTableColumns(visitLog),
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

    const result = await this.db
      .select({
        ...getTableColumns(visit),
        visitLog: getTableColumns(visitLog),
      })
      .from(visit)
      .innerJoin(visitLog, eq(visit.visitId, visitLog.visitId))
      .where(and(eq(visitLog.status, "pending"), eq(visit.userId, userId)));

MAST•16mo ago

I think you can just set the visitLog: visitLog and it should work. According to the documentation: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] – DrizzleORM

Drizzle ORM | %s

Gaming

Programming

one to one query