Drizzle Team•9mo ago

Trouble using findMany with joined relation and where clause on relation

Hello, I'm trying to construct a query using the findMany function of drizzle, and perform a simple join to a second table with a foreign key relation and include a 'where' clause that keys on one of the columns of the joined table. My current query is constructed as follows:

  const response = await db.query.tableOne.findMany({
    where: and(gt(tableOne.createdAt, createdAfter), eq(tableOne.createdBy, userId)),
    with: {
      tableTwoEntity: {
        where: and(eq(tableTwo.organizationId, organizationId)),
      }
    },
    orderBy: [desc(tableOne.createdAt)],
    limit,
  })

  const response = await db.query.tableOne.findMany({
    where: and(gt(tableOne.createdAt, createdAfter), eq(tableOne.createdBy, userId)),
    with: {
      tableTwoEntity: {
        where: and(eq(tableTwo.organizationId, organizationId)),
      }
    },
    orderBy: [desc(tableOne.createdAt)],
    limit,
  })

I am querying against tableOne, which I have a relation to tableTwo defined for in my schema definition, with tableTwoEntity being the named entity of the relation. I would like to include a number of filters on tableOne, but additionally filter by tableTwo on one of the columns. For some reason Drizzle gives me a typescript error stating that the Object literal may only specify known properties, and 'where' does not exist in type .... I believe this is the best practice way to do things, but would appreciate any guidance if I'm not following the correct best practice for doing this type of combined where clause in a findMany query. Thank you

6 Replies

piechart•9mo ago

This is only valid if the relation is defined with many(), i.e. there are many related rows in tableTwo for each row in tableOne and you want to only return some of them. If the relation is one-to-one the nested "where" does not make sense. Your options are: 1) custom "where" condition at the top level with raw sql, something like exists (select * from tableTwo where joinColumn = ${tableOne.joinColumn} and organizationId = ${organizationId}) 2) rewrite as an innerJoin without query API 3) filter the result in javascript instead

cssetianOP•9mo ago

I see, thanks for the response. I will say this feels slightly suboptimal from a query api perspective, that when writing a findMany query you're only limited to a certain subset of where clauses that can be performed on joined tables. You're basically limited from using any kind of joins and filters on joins when writing queries using that funciton, and it seems to be the recommended approach for composing generalized queries, from what i've read at least. Would be great for this query api to support where() clauses nested within 'with' parameters in the findMany function

cssetianOP•9mo ago

https://orm.drizzle.team/docs/rqb#select-filters - this section of the documentation seems to point out to this being possible though

Drizzle ORM - Query

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

cssetianOP•9mo ago

await db.query.posts.findMany({ where: (posts, { eq }) => (eq(posts.id, 1)), with: { comments: { where: (comments, { lt }) => lt(comments.createdAt, new Date()), }, }, });

piechart•9mo ago

Yes it's possible with a many() relation. But it only filters the related comments, not the posts at the top level. So it's not the same behavior as the one you were looking for with the join.

cssetianOP•9mo ago

I see, thanks for the clarification it would be great to note this in the docs there, it was confusing to figure out just by looking at the example

Gaming

Programming

Trouble using findMany with joined relation and where clause on relation