Drizzle Team•11mo ago

can we filter by a prop on a relation during a query?

eg. watch has watch brands + watch families as relations if i wanted to search across watch name, watch brand name + watch family name should that be possible? current query just using watch name

const items = await ctx.db.query.watch.findMany({
            ...withCursorPagination({
              limit: 10,
              cursors: [[schema.watchBrand.name, "desc", cursor]],
            }),
            where: and(
              like(schema.watch.name, `%${input.name}%`),
              notInArray(schema.watch.id, usersWatches),
            ),
            with: {
              watchBrand: true,
              watchFamily: true,
            },
          });

const items = await ctx.db.query.watch.findMany({
            ...withCursorPagination({
              limit: 10,
              cursors: [[schema.watchBrand.name, "desc", cursor]],
            }),
            where: and(
              like(schema.watch.name, `%${input.name}%`),
              notInArray(schema.watch.id, usersWatches),
            ),
            with: {
              watchBrand: true,
              watchFamily: true,
            },
          });

4 Replies

Mykhailo•11mo ago

Hello, @Jokerz. It looks like watches have relations many-to-one with watchBrand and watchFamily (each watch is linked to just one brand and one family). This means we can't really pick and choose watches based on their brand or family because each watch only has one of each. In other situation, one-to-many or many-to-many you can filter by a prop in relation. For example, watches could belong to many brands (hypothetical) or families, then we could filter or pick watches by a certain brand or family. We could look for just the watches from the brand "Seiko" like this

// your query

// relations
with: {
  watchBrands: {
    where: eq(watchBrand.name, 'Seiko')
  }
}

// your query

// relations
with: {
  watchBrands: {
    where: eq(watchBrand.name, 'Seiko')
  }
}

https://orm.drizzle.team/docs/rqb#select-filters - example with comments

Drizzle ORM - next gen TypeScript ORM

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

JokerzOP•10mo ago

hey @Mykhailo thanks for the advice i decided to create a new field keywords and populate it with the brand + family name with the watch name have a new interesting situation

 where: ilike(schema.watch.keywords, `%${input.name}%`),

 where: ilike(schema.watch.keywords, `%${input.name}%`),

i am now using this, which works great say if a watch is called rolex daytona gold and i search rolex daytona im going to get that watch but if i search rolex gold its not going to appear there is this ilike filter only searching in order through the string or can i match multiple parts? im guessing cant match multiple parts because then a would return everything so like if i have a object with value 1 2 3 in the keywords field and search 3 2 1 it should find it? or i need to split my search term up and search for each

Mykhailo•10mo ago

Hello, @Jokerz! I think you should split you search. BTW, you might find smth useful in this chat: https://discord.com/channels/1043890932593987624/1208652455059914782

JokerzOP•10mo ago

hey, what do you mean by split my search?

Gaming

Programming

can we filter by a prop on a relation during a query?