one to one query

I have a 2 models relation one to one,

visit

visit

and

visit_log

visit_log

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 .

bobaholic•9/5/23, 11:35 PM

for the first issue, I believe they removed the ability for nested where statements in relational queries, but it still exists in older versions

bobaholic•9/5/23, 11:35 PM

and for the second statement, can you past the entire query?

SiumauricioOP•9/5/23, 11:45 PM

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

Angelelz•9/6/23, 12:54 AM

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

Angelelz•9/6/23, 12:58 AM

Angelelz•9/6/23, 12:59 AM

This should get rid of the type error

SiumauricioOP•9/6/23, 3:13 AM

Thanks @angelelz!

I modify and it works now

SSiumauricio Thanks @angelelz! I modify and it works now ```ts const result = await thi...

MAST•9/6/23, 3:40 AM

I think you can just set the and it should work.

According to the documentation: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] – DrizzleORM

Drizzle ORM | %s