Drizzle Team•2y ago

Nested where clauses in relational queries

I have this query: const asset = await db.query.assets.findFirst({ with: { assetImages : true, assetModeration: { where: eq(assetsModerations.state, "ACCEPTED"), //this line gives error columns: { state: true, }, } }, where: (eq(assets.id, id)) }) that gives this error: Object literal may only specify known properties, and 'where' does not exist in type ... the assetsModerations table looks like this: export const assetsModerations = pgTable( "assets_moderations", { id: serial('id').primaryKey(), created_at: timestamp('created_at').notNull().defaultNow(), description: text('description'), moderatorId: text("moderator_id").references(() => users.id), updatedAt: timestamp('updated_at'), state: moderationState('moderation_state').notNull().default('PENDING') } ) this error is not given if i try to do the same with assetImages, the other table, what is the problem here? Thank you in advance.

27 Replies

Andrii Sherman•2y ago

if you can try to use callback for this where? https://orm.drizzle.team/docs/rqb#select-filters

where: (table, { eq }) => eq(table.id, 1)

where: (table, { eq }) => eq(table.id, 1)

Relational queries – DrizzleORM

Drizzle ORM | %s

desertOP•2y ago

unfortunately it does not work, it just says 'where' property does not exist on assetsModerations

Noahh•2y ago

What do your relations look like? (to wrap code blocks you can use ``` on both sides of it!)

desertOP•2y ago

thank you! i'm sending them now

export const assetsRelations = relations(assets, ({one, many}) => ({
  assetModeration: one(assetsModerations, {fields: [assets.moderationId], references: [assetsModerations.id]})
}))

export const assetsRelations = relations(assets, ({one, many}) => ({
  assetModeration: one(assetsModerations, {fields: [assets.moderationId], references: [assetsModerations.id]})
}))

thats it, its a one on one relationship one asset -> one assetModeration

Noahh•2y ago

What version of drizzle-orm do you have?

desertOP•2y ago

[email protected] should i npm update? i saw a new version got released today

Noahh•2y ago

if you're able to update that'd be great, right now the latest is 0.28.5

desertOP•2y ago

ok I'm updating, trying again in a minute! thank you for the help btw <3

desertOP•2y ago

still no 'where' unfortunately :(

desertOP•2y ago

here after inserting the expression

desertOP•2y ago

the only thing that comes to my mind that could be causing this is that every column of this table is optional, when inserting i leave the values blank, most of them have defaults

Noahh•2y ago

What if you try what Andrew suggested (replace eq(assets.assetCategoryId, category.id) with (table) => eq(assets.assetCategoryId, category.id)) if that doesn't work, can you send your assets schema?

desertOP•2y ago

you mean like this?

Noahh•2y ago

sorry, misread the line. replace the line with the error with that

desertOP•2y ago

yes sure, here it is:

export const assets = pgTable(
  'assets',
  {
    id: serial('id').primaryKey(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    description: text('description').notNull(),
    priceCents: integer('price_cents').notNull(),
    name: text('name').notNull(),
    assetCategoryId: integer('asset_category_id').notNull(),
    authorId: text('author_id').notNull(),
    thumbnailUrl: text('thumbnail_url'),
    thumbnailKey: text('thumbnail_key'),
    moderationId: integer('moderation_id').notNull(),
    assetFileKey: text('asset_file_key').notNull().unique(),
    assetFileUrl: text('asset_file_url').notNull(),
  },
  (t) => ({
    unq: unique().on(t.name, t.authorId)
  })
)

export const assets = pgTable(
  'assets',
  {
    id: serial('id').primaryKey(),
    created_at: timestamp('created_at').notNull().defaultNow(),
    description: text('description').notNull(),
    priceCents: integer('price_cents').notNull(),
    name: text('name').notNull(),
    assetCategoryId: integer('asset_category_id').notNull(),
    authorId: text('author_id').notNull(),
    thumbnailUrl: text('thumbnail_url'),
    thumbnailKey: text('thumbnail_key'),
    moderationId: integer('moderation_id').notNull(),
    assetFileKey: text('asset_file_key').notNull().unique(),
    assetFileUrl: text('asset_file_url').notNull(),
  },
  (t) => ({
    unq: unique().on(t.name, t.authorId)
  })
)

Noahh•2y ago

so it'd be (table) => eq(table.state, "ACCEPTED")

desertOP•2y ago

still getting the error :(

Noahh•2y ago

@Andrew Sherman I'm able to reproduce this in the drizzle-playground repo, with and without the callback syntax. Not sure where to go from there. Can send a sample repo in a bit.

Andrii Sherman•2y ago

@Dan Kochetov just in case you can spot an issue quickly

Dan•2y ago

every time you cannot filter a one relation

Andrii Sherman•2y ago

🫡

Noahh•2y ago

🤦‍♂️makes sense

desertOP•2y ago

i did not mean to filter the relationship, i did not understand well how to use relational queries, what i want to do is to filter every asset for which its moderation is not accepted, i want to filter the top layer table every asset has one assetModeration, i want to filter out assets that do not have assetModeration.state === "accepted"

Noahh•2y ago

Unfortunately filtering by nested relations is not currently possible. You'll have to either run the query and filter by the nested relations on the code side, or you'll have to use the Core API and joins/filter "manually"

desertOP•2y ago

ahhh thank you. Do you know if this is a feature that is planned for the future or not?

Dan•2y ago

we're discussing it internally for now, it's possible it'll be implemented in some form in the future

desertOP•2y ago

ok thank you!

Gaming

Programming

Nested where clauses in relational queries

Did you find this page helpful?