Drizzle Team•16mo 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•16mo 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•16mo ago

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

Noahh•16mo ago

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

desertOP•16mo 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•16mo ago

What version of drizzle-orm do you have?

desertOP•16mo ago

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

Noahh•16mo ago

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

desertOP•16mo ago

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

desertOP•16mo ago

still no 'where' unfortunately :(

desertOP•16mo ago

here after inserting the expression

desertOP•16mo 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•16mo 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•16mo ago

you mean like this?

Noahh•16mo ago

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

desertOP•16mo 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•16mo ago

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

desertOP•16mo ago

still getting the error :(

Noahh•16mo 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•16mo ago

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

Dan•16mo ago

every time you cannot filter a one relation

Andrii Sherman•16mo ago

🫡

Noahh•16mo ago

🤦‍♂️makes sense

desertOP•16mo 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•16mo 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•16mo ago

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

Dan•16mo ago

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

desertOP•16mo ago

ok thank you!

Gaming

Programming

Nested where clauses in relational queries