Drizzle Team•17mo ago

Sub-field Filtering example

In the 0.28.0 update that removed relation filters, the changelog suggests: https://github.com/drizzle-team/drizzle-orm/releases/tag/0.28.0

If you have used those fields in the where callback before, there are several workarounds: 1. Applying those filters manually on the code level after the rows are fetched; 2. Using the core API.

I'd rather not apply the filter in the code level. Could someone provide an example of "2. Using the core API." to achieve a basic top level filter based on a sub-field property?

GitHub

Release 0.28.0 · drizzle-team/drizzle-orm

Breaking changes Removed support for filtering by nested relations Current example won't work in 0.28.0: const usersWithPosts = await db.query.users.findMany({ where: (table, { sql }) => (...

2 Replies

Noahh•17mo ago

The core API is using db.select().from(table).where(...) instead of db.query.table.findFirst(). I'd imagine in this case, you'd want to have foreign keys set up instead of Drizzle's RQB relations, then use joins, referencing those joins in your where query? https://orm.drizzle.team/docs/joins

export const users = pgTable("users", {
    id: serial("id").primaryKey(),
    name: text("full_name"),
});

export const posts = pgTable("posts", {
    id: serial("id").primaryKey(),
    title: text("title"),
    authorId: integer("author_id")
        .notNull()
        .references(() => users.id),
});

export const users = pgTable("users", {
    id: serial("id").primaryKey(),
    name: text("full_name"),
});

export const posts = pgTable("posts", {
    id: serial("id").primaryKey(),
    title: text("title"),
    authorId: integer("author_id")
        .notNull()
        .references(() => users.id),
});

If you wanted to get all posts where the author's name is "Joe" it might be something like this:

// Adding some users and posts to the db
await db.insert(users).values([{ name: "Jim" }, { name: "Todd" }, { name: "Joe" }]);

await db.insert(posts).values([
    { title: "Hi I'm Jim", authorId: 1 },
    { title: "Hi I'm Todd", authorId: 2 },
    { title: "Hi I'm Joe", authorId: 3 },
    { title: "Hi I'm Jim 2", authorId: 1 },
    { title: "Hi I'm Todd 2", authorId: 2 },
    { title: "Hi I'm Joe 2", authorId: 3 },
]);

// Adding some users and posts to the db
await db.insert(users).values([{ name: "Jim" }, { name: "Todd" }, { name: "Joe" }]);

await db.insert(posts).values([
    { title: "Hi I'm Jim", authorId: 1 },
    { title: "Hi I'm Todd", authorId: 2 },
    { title: "Hi I'm Joe", authorId: 3 },
    { title: "Hi I'm Jim 2", authorId: 1 },
    { title: "Hi I'm Todd 2", authorId: 2 },
    { title: "Hi I'm Joe 2", authorId: 3 },
]);

console.log(
    await db
        .select({
            ...getTableColumns(posts)
        })
        .from(posts)
        .leftJoin(users, eq(posts.authorId, users.id))
        .where(eq(users.name, "Joe")),
    );

console.log(
    await db
        .select({
            ...getTableColumns(posts)
        })
        .from(posts)
        .leftJoin(users, eq(posts.authorId, users.id))
        .where(eq(users.name, "Joe")),
    );

gets you

[
  { id: 3, title: "Hi I'm Joe", authorId: 3 },
  { id: 6, title: "Hi I'm Joe 2", authorId: 3 }
]

[
  { id: 3, title: "Hi I'm Joe", authorId: 3 },
  { id: 6, title: "Hi I'm Joe 2", authorId: 3 }
]

Let me know if that makes sense!

Joins [SQL] – DrizzleORM

Drizzle ORM | %s

IstoraMandiriOP•17mo ago

Ah, got it. Thank you so much @Noahh !

Gaming

Programming

Sub-field Filtering example