Drizzle Team•2w ago

Filtering Entire Objects by Related Records in Drizzle ORM Query API

Hi, Is there currently functionality with Drizzle's query API to filter entire objects based on the contents of their relationships, rather than just filtering the related objects themselves. Specifically, I want to filter the main object (sourceTable) based on a condition in its related object (userToSource). Here is an example of what I'm currently doing:

const sources = await db.query.sourceTable.findMany({
    with: {
        signups: true, 
        userToSource: {
            where: (userToSource, {eq}) => eq(userToSource.userId, user.id),
        }
    },
});

const sources = await db.query.sourceTable.findMany({
    with: {
        signups: true, 
        userToSource: {
            where: (userToSource, {eq}) => eq(userToSource.userId, user.id),
        }
    },
});

In this example, I want the entire sourceTable object to be returned only if the userToSource relationship meets the specified condition (userToSource.userId === user.id). However, it seems that the current functionality only filters the userToSource relationship itself, not the main sourceTable object. Thank in advance!

6 Replies

EvanstonOP•2w ago

Is the current best implementation to just filter out the sources variable after the fact?

TOSL•2w ago

Try a where subquery on the source table share schema for more help

EvanstonOP•2w ago

Can you please elaborate on what you mean by this? Here is my schema

import { primaryKey, pgTable, serial, text, integer, pgEnum, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const sessionTable = pgTable("session", {
    id: text("id").primaryKey(),
    userId: integer("user_id")
        .notNull()
        .references(() => userTable.id),
    expiresAt: timestamp("expires_at", {
        withTimezone: true,
        mode: "date"
    }).notNull()
});

export const userTable = pgTable('user', {
    id: serial('id').primaryKey(),
    fname: text('fname').notNull(),
    lname: text('lname').notNull(),
    email: text('email').notNull().unique(),
    password: text('password').notNull(),
});
export const userRelations = relations(userTable, ({ many }) => ({
    userToSource: many(userSourceTable),
    signups: many(userSignupStatusTable)
}));

export const sourceTable = pgTable("source", {
    id: serial('id').primaryKey(),
    url: text('url').unique(),
});
export const sourceRelations = relations(sourceTable, ({ many }) => ({
  userToSource: many(userSourceTable),
  signups: many(signupTable)
}));

export const userSourceTable = pgTable(
  'users_to_source',
  {
    userId: integer('user_id')
      .notNull()
      .references(() => userTable.id),
    sourceId: integer('source_id')
      .notNull()
      .references(() => sourceTable.id, {onDelete: 'cascade'}),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.sourceId] }),
  }),
);
export const usersToSourceRelation = relations(userSourceTable, ({ one }) => ({
  source: one(sourceTable, {
    fields: [userSourceTable.sourceId],
    references: [sourceTable.id],
  }),
  user: one(userTable, {
    fields: [userSourceTable.userId],
    references: [userTable.id],
  }),
}));

import { primaryKey, pgTable, serial, text, integer, pgEnum, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const sessionTable = pgTable("session", {
    id: text("id").primaryKey(),
    userId: integer("user_id")
        .notNull()
        .references(() => userTable.id),
    expiresAt: timestamp("expires_at", {
        withTimezone: true,
        mode: "date"
    }).notNull()
});

export const userTable = pgTable('user', {
    id: serial('id').primaryKey(),
    fname: text('fname').notNull(),
    lname: text('lname').notNull(),
    email: text('email').notNull().unique(),
    password: text('password').notNull(),
});
export const userRelations = relations(userTable, ({ many }) => ({
    userToSource: many(userSourceTable),
    signups: many(userSignupStatusTable)
}));

export const sourceTable = pgTable("source", {
    id: serial('id').primaryKey(),
    url: text('url').unique(),
});
export const sourceRelations = relations(sourceTable, ({ many }) => ({
  userToSource: many(userSourceTable),
  signups: many(signupTable)
}));

export const userSourceTable = pgTable(
  'users_to_source',
  {
    userId: integer('user_id')
      .notNull()
      .references(() => userTable.id),
    sourceId: integer('source_id')
      .notNull()
      .references(() => sourceTable.id, {onDelete: 'cascade'}),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.sourceId] }),
  }),
);
export const usersToSourceRelation = relations(userSourceTable, ({ one }) => ({
  source: one(sourceTable, {
    fields: [userSourceTable.sourceId],
    references: [sourceTable.id],
  }),
  user: one(userTable, {
    fields: [userSourceTable.userId],
    references: [userTable.id],
  }),
}));

bump

TOSL•2w ago

So if I understand you correctly, you want to use a where condition that relies on the related table which the relation/query api has some restricting limitations on in the current implementation. These limitations are reducing if not going away entirely in V2 on the api. https://github.com/drizzle-team/drizzle-orm/discussions/2316 For now, you can do something like (not exact for your use case I'm just pasting in the same example I gave someone else)

const subquery = db.select({ id: info.userId }).from(info).where(eq(info.orgId, 1)))

const result = await db.query.users.findMany({
  where: inArray(users.id, subquery,
  with: {
    info: true
  },
});

const subquery = db.select({ id: info.userId }).from(info).where(eq(info.orgId, 1)))

const result = await db.query.users.findMany({
  where: inArray(users.id, subquery,
  with: {
    info: true
  },
});

Your use case might look something like:

const subquery = db.select({id: userToSource.sourceId}).from(userToSource).where(eq(userToSource.userId, *fill in user id*

const result = await db.query.sourceTable.findMany({
  where: inArray(source.id, subquery)
  with: {
  signups: true
}
})

const subquery = db.select({id: userToSource.sourceId}).from(userToSource).where(eq(userToSource.userId, *fill in user id*

const result = await db.query.sourceTable.findMany({
  where: inArray(source.id, subquery)
  with: {
  signups: true
}
})

Probably not exactly right so you'll have to tinker

GitHub

Relational API v2 · drizzle-team drizzle-orm · Discussion #2316

We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and goi...

TOSL•2w ago

or just use select with joins

Drizzle ORM - Select

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

EvanstonOP•2w ago

Ok thanks! This was very helpful.

Gaming

Programming

Filtering Entire Objects by Related Records in Drizzle ORM Query API

Did you find this page helpful?