DT
Drizzle Team•10mo ago
Marc

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
Mykhailo•10mo 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
MarcOP•10mo 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
Mykhailo•10mo ago
Super! Could you share the schema please? I will try to do it with relational queries

Did you find this page helpful?