Drizzle Team•5mo ago

Can I use "where" clause on joins ?

Hello ! I'm trying to query some data based on conditions. I know how to do it in prisma but I don't find a solution in drizzle. In Prisma my query looks like:

const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await prisma.model.findFirst({
  where: {
    id: modelId,
    task: {
      dataset: {
        userDatasets: {
          some: {
            userId,
          },
        },
      },
    },
  },
});

const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await prisma.model.findFirst({
  where: {
    id: modelId,
    task: {
      dataset: {
        userDatasets: {
          some: {
            userId,
          },
        },
      },
    },
  },
});

In Drizzle I have currenlty this:

const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await db.query.model.findFirst({
  where({id}){
    return eq(id, modelId)
  }
})

const modelId = "dummyModelId";
const userId = "dummyUserId";

const data = await db.query.model.findFirst({
  where({id}){
    return eq(id, modelId)
  }
})

I've tried this:

const data = await db.query.model.findFirst({
  where({id}){
    return eq(id, modelId)
  },
  with:{
    task:{
      with:{
        dataset:{
          with:{
            userDatasets:{
              // 'where' clause not possible here
            }
          }
        }
      }
    }
  }
})

const data = await db.query.model.findFirst({
  where({id}){
    return eq(id, modelId)
  },
  with:{
    task:{
      with:{
        dataset:{
          with:{
            userDatasets:{
              // 'where' clause not possible here
            }
          }
        }
      }
    }
  }
})

I would like to use 'where' clause on fields that are in other tables. Thanks for the help ! 😄

3 Replies

Mykhailo•5mo ago

Hello, you can, but if there is many relation. https://orm.drizzle.team/docs/rqb#select-filters

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Marc•5mo ago

Thanks ! I've done it like that:

const data = await db
  .select()
  .from(model)
  .leftJoin(task, eq(model.taskId, task.id))
  .leftJoin(dataset, eq(task.datasetId, dataset.id))
  .where(
    and(eq(model.id, modelId), eq(dataset.userDatasetId, userDatasetId))
  );

const data = await db
  .select()
  .from(model)
  .leftJoin(task, eq(model.taskId, task.id))
  .leftJoin(dataset, eq(task.datasetId, dataset.id))
  .where(
    and(eq(model.id, modelId), eq(dataset.userDatasetId, userDatasetId))
  );

Seems to work 🙂

Mykhailo•5mo ago

Super! Could you share the schema please? I will try to do it with relational queries

Gaming

Programming

Can I use "where" clause on joins ?