Add nested filters for relational queries

[0xboby]

I would like to create queries that filter by single values from a nested partial fields select.

Imagine we have a users table with a one-to-one relation to a profile. The profiles table has a likes and a createdAt columns that store the amount of likes and the date the profile was created, respectively.

In this example I want to get all the users named "john" whose profile has more than 5 likes and was created within the last 30 days.

  await db.query.users.findMany({
    where: (users, { eq }) => eq(users.name, "john"),
    with: {
      profile: {
        columns: {
          likes: {
            where: (value, { gt }) => gt(value, 5),
          },
          createdAt: {
            where: (value, { gt }) => gt(value, lastMonth),
          },
        },
      },
    },
  });

This is currently not possible with Drizzle. The workaround I found is filtering after getting the result from the database.

  await db.query.users.findMany({
    where: (users, { eq }) => eq(users.name, "john"),
    with: {
      profile: {
        columns: {
          likes: true
          createdAt: true
        },
      },
    },
  }).then((payload) => payload.filter(
    ({ profile }) =>
      profile.likes > 5 &&
      profile.createdAt > lastMonth,
  ));

This is not ideal because I have to read more rows than I need to. Is there a good reason to do things this way? Is it more performant?

---

UPDATE: Omar McAdam, provided a solution/workaround. This is how you could use it to filter in this example:

await db.query.users.findMany({
  where: and(
      eq(users.name, "john"),
      inArray(
        users.id,
        db
          .select({ id: profiles.userId })
          .from(profiles)
          .where(
              and(
                gt(profiles.likes, 5),
                gt(profiles.createdAt, lastMonth),
              ),
            ),
      )
  ),
  with: {
    profile: {
      columns: {
        likes: true
        createdAt: true
      },
    },
  },
});

[typeofweb]

Any reason you've opened this as a discussion instead of issue?

[0xboby]

Since this feature doesn't exist, I thought it wasn't an "issue". That's why I placed it in the ideas category. I can move it there if you think that's appropriate.

[205g0]

But you find nested where:s in the docs, so it should be a supported feature.

[alanhamlett]

No, that's not querying the resulting joined table... it's returning Posts with id 1 and Comments for those posts, where Comments are filtered against the date before joining with Posts table. That's different from returning Posts that have comments before the date.

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[205g0]

This should go into the docs, I'm hitting this problem myself just now, and I assumed it's me getting the syntax not right, because--and this is good--TS complains about nested where:s.

And this is kind of a dealbreaker because this things reads and sends the entire db for even most simple use cases/queries. Hope, I got something wrong.

Edit: But the docs state examples with nexted where:s, so it should be possible, or not? Confused...

[0xboby]

You can filter entire tables, but not values in a table. For example, you can filter a user's posts, but not the amount of likes on each post.

[205g0]

Sure?? Another example: one user => many posts, one post => many comments, query: show me all comments a user ever received

the query would be similar to yours: you start with comments, nest posts, then users or just stop at posts and do a where posts.userId = 1

should work in theory, and in the docs but doesn't

with your example it should show users which fulfill your condition/wheres

edit: no i am wrong, you query is different, you would need to turn your query upside down

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[rimai4]

I'm running into the same issue. It's not possible to specify where for nested tables, although the docs indicate that it should be possible (second example for https://orm.drizzle.team/docs/rqb#select-filters).

[gabrielDonnantuoni]

I was trying to follow the exact same section of the docs and had no success as well.

[alanhamlett]

It's querying before the join not after... for ex it's doing 1 but we assume it does 2:

1. Return Posts with Comments filtered to created before date
2. Return Posts filtered to Posts having Comments created before date

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[avnav0]

same issue, pretty upset because i caught it by accident:

                let formIds = await tx.query.formInputsTable.findMany({
                    columns: {
                        formId: true
                    },
                    with: {
                        user: {
                            where: and(
                                eq(formUsersTable.oauthId, oauthId),
                                isNotNull(formUsersTable.oauthId)
                            )
                        }
                    },
                });

gives me results like:

    {
        "formId": 34,
        "user": null
    },

going to drop the relational queries and use normal sql i guess?

[0xboby]

You can still filter the result using the workaround with .then() that I showed. The problem is that you read more data than you need from the database.

[avnav0]

yeah i was using .filter()

but i rather just use pure sql if this is the case. however, that's not working out for me...

[205g0]

I'd use joins, that's what there are made for

FYI, I did a small comparison table here: #1226

[foodornt]

I can't let go of the convenient .query builder, I wouldn't go for joins
I wish with: allowed inner joins

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[nagy135]

is this getting worked at?
I mean this scenario of A/B where (A has-many B) and we want to select all B where "connected A has my user id" for example .....this is basically in every app I can imagine. Is this in making or do you guys switch ORM ?

[alanhamlett]

UPDATE: Omar McAdam, provided a solution/workaround.

The IN filter solution will be very slow on large tables. Support for querying relationships with auto joins is the way to go, but until then we're stuck using Drizzle Core to build the join manually.

[pencilcheck]

I believe that is how any ORM will implement internally anyway. This is an inherent limitation of SQL. A.k.a. If you remove drizzle you have to do something similar anyway.

At least you are forced to learn how SQL actually works if you don't want to create indexing and intermediate tables (or materialized views or views) to speed things up.

If drizzle can add some QoL to make things optimized by default that would be awesome. E.g. it automatically create a materialized view for the sub query, or add indexing for the table used in the subquery

[nickludlam]

I'd really appreciate this feature too. If I fall back to using the full db.select().from().leftJoin().where() I lose the expression convenience AND I lose the table name conversion, so I've got a completely unique naming convention for where clauses using joined tables.

[AlexBlokh]

that's now in progress

[gediminastub]

Are there any news? Thanks!

[joedelia]

+1 on this

[nextglabs]

+1

[sahrohit]

+1

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[seanaguinaga]

I think this is related - I can't use offset pagination with this because it returns a user per each role it finds so it returns duplicate users

Am I writing this query wrong?

[davidchalifoux]

The same way the docs recommend. Order by some column. Limit the number of rows returned. Set the offset to something like offset = pageSize * pageNumber.

[seanaguinaga]

That's what I am saying the issue is with this

With offset pagination it doesnt return everything it should because duplicate records are returned

[AlexBlokh]

you can use relational queries if you want to omit joins

[AlexBlokh]

you can now follow a Drizzle Queries V2 discussion and if you have any suggestions - feel free to comment there, we'll gladly patch while we're implementing

[dave-palt]

I think this is related - I can't use offset pagination with this because it returns a user per each role it finds so it returns duplicate users

Am I writing this query wrong?

you can use selectDistinct and specify only the fields of the main entity

Your current query

export async function test() {
    return db
        .select({
            id: things.id,
            item: items.id,
        })
        .from(things)
        .where(buildWhere(things, { _eq: { id: 2 } }))
        .leftJoin(items, eq(things.id, items.card))
        .limit(pageSize)
        .offset(0);
}

Result:

[{"id":2,"item":13},{"id":2,"item":81},{"id":2,"item":101}]

Query with distinct

export async function test() {
    return db
        .selectDistinct({
            id: things.id,
            ...additionalFieldsInThings
        })
        .from(things)
        .where(buildWhere(things, { _eq: { id: 2 } }))
        .leftJoin(items, eq(things.id, items.card))
        .limit(pageSize)
        .offset(0);
}

Result:

[{"id":2}]

With this approach you cannot populate references directly.

Here are some alternatives:

• select ids paginated and then execute another query by id with relationships populated
• add this as condition of your query with relationships populated
• group by the main table and use jsonb_agg to get subfields populated

Here is an example for the last option which I tried myself out of curiosity

export async function test() {
    return db
        .selectDistinct({
            id: things.id,
            items: sql`jsonb_agg(${items})`
        })
        .from(things)
        .leftJoin(items, eq(things.id, items.thing))
        .groupBy(things.id)
        .limit(2)
        .offset(offset)
        ;
}

Result with offset 0

[{"id":1,"items":[{"id":11}]},{"id":2,"items":[{"id":13},{"id":81},{"id":101}]}]

Result with offset 1 (so we can double check id = 2 appears in both places

[{"id":2,"items":[{"id":13},{"id":81},{"id":101}]},{"id":3,"items":[{"id":6}]}]

This is my first comment, I hope everything is fine